IDS Forum

RE: weirdness with temp tables

Posted By: John Adamski (Work Account)
Date: Friday, 6 May 2016, at 2:54 p.m.

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?
> > >
> > > 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 works... but I don't check it frequently...

--001a1141bb9887687005321996da

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

IDS Forum is maintained by Administrator with WebBBS 5.12.