Using global temporary tables -- MIDRANGE-L

Hello all,

I have a program where I'm making use of global temporary tables. This is my first experience with them, believe it or not, in a 36-year career. I tested out the queries I wanted to use by putting them in the Run SQL Scripts function in ACS and running them there. All worked fine. I then put them into my RPG application program and tested it and got different results for one of the queries. I've pored over the SQL statements and unless I'm totally blind they are identical. Yet, the query that builds the BINDATES table in my application program yields a slightly different (and inaccurate) result set than the one in Run SQL Scripts. Here's the SQL:

drop table bintot;

drop table bindates;

declare global temporary table bintot

(fipart char(20),
filocn char(3),
fibin char(10),
fiqtysum
dec(15,5))
on commit delete rows;

declare global temporary table bindates

(fipart char(20),
filocn char(3),
fibin char(10),
fidate date)
on commit delete rows;

insert into bintot

(select fipart, filocn, fibin, sum(fiqty) from fifohist
where fipart = :fgpart and fibin <> ' '
group by fipart, filocn, fibin
order by fipart, filocn, fibin);

insert into bindates

(select fipart, filocn, fibin, min(ficrtdt) from fifohist
where fipart = :fgpart and fibin <> ' '
group by fipart, filocn, fibin);

I then declare a cursor for the following statement and load the results into a data structure:

select t.fipart, t.filocn, t.fibin, t.fiqtysum, d.fidate from bintot T
join bindates D on t.fipart = d.fipart and t.filocn = d.filocn and t.fibin = d.fibin
order by fidate;

Any guesses out there on what I might be doing wrong? Is there something with using global temp tables in a program that differentiates it from running it in Run SQL Scripts that I'm missing? Any suggestions would be most welcome. Thanks and Happy Holidays!

Bill Howie[Related image]<https://www.hc-companies.com/>
Senior ERP Programmer/Analyst
Office: 330.495.5627 | Cell: 330.495.5627 | Toll Free: 800.225.7712
2450 Edison Blvd., Suite 3, Twinsburg, OH 44087
hc-companies.com<https://www.hc-companies.com>
Leader in Horticultural Containers
[cid:image003.png@01D9146B.CA16D350]<https://www.linkedin.com/company/the-hc-companies> [cid:image004.png@01D9146B.CA16D350] <https://www.facebook.com/HCCompanies/> [cid:image005.png@01D9146B.CA16D350] <https://www.instagram.com/hccompanies/> [cid:image006.png@01D9146B.CA16D350] <https://twitter.com/hc_companies>

Disclaimer

The information contained in this communication from the sender is confidential. It is intended solely for use by the recipient and others authorized to receive it. If you are not the recipient, you are hereby notified that any disclosure, copying, distribution or taking action in relation of the contents of this information is strictly prohibited and may be unlawful.

This email has been scanned for viruses and malware, and may have been automatically archived by Mimecast Ltd, an innovator in Software as a Service (SaaS) for business. Providing a safer and more useful place for your human generated data. Specializing in; Security, archiving and compliance. To find out more visit the Mimecast website.