RE: weirdness with temp tables

I hope the formatting stay otherwise might be a mess to read. below are the SELECTs in question that this week started to work differently. "$argv[1]" is which bal_prd or pay period we are running the script for. -- find all ids/wks in this bal_prd select unique t.bal_prd, t.id, t.cal_wk, t.job_no from gu_stu_timesheets t, gu_timesheets g where t.bal_prd = "$argv[1]" and t.bal_prd=g.bal_prd and t.id=g.id and -- t.job_no=g.job_no and g.approved = "Y" and t.reg_hrs != 0 and t.job_no=g.job_no and t.reg_hrs != 0 and g.processed != "Y" group by 1,2,3,4 into temp stu_id_wks with no log; -- calaculate the total hours worked for the id/week by job ignore the bal_prd select s.bal_prd, s.id, s.cal_wk, s.job_no, nvl(sum(t.reg_hrs),0) tot_reg, max(hrtime_date) end_of_wk from gu_stu_timesheets t, stu_id_wks s -- ignore bal_prd where s.id=t.id and s.cal_wk=t.cal_wk and -- look at whole week hrtime_date > add_date_proc(TODAY,-2,"MONTH","TRUNC") -- prevent multiple years data and t.job_no = s.job_no -- total by job and t.job_no in (select job_no from stu_id_wks where id = t.id) -- approved jobs group by 1,2,3,4 into temp stu_wk_tot_a with no log; -- calaculate the total hours worked for the id/week ignore the bal_prd select bal_prd, id, cal_wk, nvl(sum(tot_reg),0) tot_reg, max(end_of_wk) end_of_wk from stu_wk_tot_a where job_no in (select job_no from stu_id_wks where id = stu_wk_tot_a.id) -- approved jobs group by 1,2,3 into temp stu_wk_tot with no log; -- If the total for the full week is 40 or less no OT all is REG time -- select these job/week records and store in temp table select s.bal_prd, s.id, s.cal_wk, s.tot_reg, s.end_of_wk from stu_wk_tot s where tot_reg <= 40 into temp stu_no_ot with no log; -- calculate this bal_prd total REG time for each job/week select s.bal_prd, s.id, t.job_no, s.cal_wk, sum(t.reg_hrs) reg_hrs, s.end_of_wk from stu_no_ot s, gu_stu_timesheets t where t.bal_prd = "$argv[1]" and t.bal_prd=s.bal_prd and t.id=s.id and --added cast to fix weirdness t.cal_wk=s.cal_wk and t.job_no in (select job_no from stu_id_wks where id = t.id) -- approved jobs group by 1,2,3,4,6 into temp stu_no_ot_1; Normally we get one record per week per id but this week we got the multiple records per week per id. So if the person had 3 weeks of pay would get 3 records for each week or 9 records. the developer replaced the WHERE clasue with this line to get it to work - cast only thing differnt. where t.bal_prd = "$argv[1]" and t.bal_prd=s.bal_prd and t.id= cast(s.id as INT) and I've looked at the code so much I can't find anything that would cause the problem. john -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando Nunes Sent: Thursday, May 05, 2016 10:31 AM To: ids@iiug.org Subject: Re: weirdness with temp tables [37090] 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? > > >@@NL