Re: weirdness with temp tables

I find it hard to understand that the type definitions of an INTO TEMP come from the data, and not from the data source. To try to be a bit more clear, if you have CREATE TABLE tab1 (col1 CHAR(10)); and... INSERT INTO tab1 VALUES ('1'); and you run: SELECT * FROM tab1 INTO TEMP test; the "test" table will have a CHAR(10) not a numeric filed. However, if your SELECT expression is a bit more complex you must check if they include any expression that could have changed... Regards. On Thu, May 5, 2016 at 2:34 PM, John Adamski (Work Account) < adamski@graceland.edu> wrote: > I looked at the script and it is using select .... into temp. I will talk > to > the developer and see if he wants to try making the temp tables first to > see > if that works. > > I will also ask him to check each select statement that creates a temp > table > to see if it is possible we are getting something to change the ID to a > character field. > > John > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Wednesday, May 04, 2016 3:39 PM > To: ids@iiug.org > Subject: Re: weirdness with temp tables [37086] > > I was speaking to John Miller about this one. An additional question: > > - How was the temp table created? Was it created by a CREATE TEMP TABLE > > statement with explicit types or by an implicit create perhaps from a > > SELECT .... INTO TEMP;? > > If the latter, then it might be that the inputs included a non-numeric > character in the id field in the first selected row. That might have caused > the temp table to have the id column as CHAR instead of INT. > > Art > > Art S. Kagel, President and Principal Consultant ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do those > opinions reflect those of other individuals affiliated with any entity with > which I am affiliated nor those of the entities themselves. > > On Wed, May 4, 2016 at 2:15 PM, John Adamski (Work Account) < > adamski@graceland.edu> wrote: > > > We agree shouldn't change the resulting set and neither should the 'fix' > > made > > a difference. Art thinks this might be a known bug so I'm going to > > pursue that avenue. > > > > John > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Fernando Nunes > > Sent: Wednesday, May 04, 2016 11:19 AM > > To: ids@iiug.org > > Subject: Re: weirdness with temp tables [37082] > > > > I would need more debug data... > > The only thing I can say is that Informix is rather smart when trying > > to compare numeric and char fields. Within this "intelligence" it > > really optimizes the queries if that don't risk having a wrong result > set. > > > > So, what I'm trying to say is that if your data changes (length of > > fields for > > example) it may change the query plans. But it should not change the > > result set. > > > > Regards. > > > > On Wed, May 4, 2016 at 3:36 PM, John Adamski (Work Account) < > > adamski@graceland.edu> wrote: > > > > > HPUX 11.31 on HP BL870c > > > IDS 12.10.FC5 > > > > > > The script hasn't been changed recently (3-4 payrolls ran since last > > > change with no problem), no database changes to tables used in the > > > script and no changed to IDS. > > > > > > We have a weird situation here that is baffling us. There is a HR > > > script that generates a number of temp tables to calculate OT and > > > weekly hours worked. > > > Been running for a number of years without problems. Ran ok about a > > > week ago, but on Monday it did not properly run. > > > > > > Is there a way to see what the database thinks the fields are in a > > > temp table? > > > > > > I ask, as we are wondering if somehow the fields from the different > > > database tables that make up the temp tables somehow got the field > > > definitions changed. > > > Like maybe the ID integer field got somehow changed to a character > filed. > > > > > > What happened was, it duplicated information as if one of the "WHERE" > > > clauses > > > didn't work on the last statement. We got the script working by > > > adding a cast > > > (integer) on the ID field of the temp table. The ID field in the > > > database table is integer and the bal_prd (pay period) and cal_wk > > > are characters fields. > > > > > > The way the script works is it starts by loading a temp table with > > > all the timesheet records for a given bal_prd (pay period) and then > > > through a number of steps using other tables to categorize the hours > > > worked (normal or OT) sums up the hours for both. The last step sum > > > up the hours per week and inserts the data into the payroll record > > > to be processed and paid. > > > > > > In the example below instead of 3 records inserted, one for each > > > week we got three records for each week (so go 9 records). The > > > selection is on ID, bal_prd and cal_wk that match the temp table and > > > the original timesheet table. > > > > > > ID bal_prd cal_wk hrs > > > 12345 STU123 15 10.000 > > > 12345 STU123 16 8.000 > > > 12345 STU123 17 10.000 > > > > > > I'm sure I'm explaining this problem poorly. > > > > > > John David Adamski, Sr. Network Specialist Graceland University, 1 > > > University Place, Lamoni, IA 50140 adamski@graceland.edu > > > 641.784.5267 > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Fernando Nunes > > Portugal > > > > http://informix-technology.blogspot.com > > My email works... but I don't check it frequently... > > > > --089e013c64129723a20532064d1f > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --047d7bfeab621cb602053209ef65 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email work