RE: SQL Statements in ILE RPG

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

This sounds like a problem I saw once....

My problem resulted from the sum() value being returned to a field that was
not sized correctly.  The values were overflowing storage and causing SQL
control fields to get corrupted.  I had to explicitely size the aggregate
function results so that I could avoid this issue.  Try the following:
C/exec sql declare c1 cursor for
C+    select cnme, bsdst#, substring(bssdat,1,6), decimal(sum(bsexbs),11,2)
<--(make this the same as iexbs)
C+      from bsf100s
C+      join brcm    on bsdst# = rcusno
C+      join rcm     on bsdst# = ccust
C+     where substring(bssdat,1,6) between :strdat and :enddat
C+       and bsrtyp = 'S'
C+     group by cnme, bsdst#, substring(bssdat,1,6)
C+     order by cnme, bsdst#, substring(bssdat,1,6)
C/end-exec

I can't remember the release we had when I saw this problem, so it might
have been fixed since then....  

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Guy Henza [mailto:guyhenza@xxxxxxxxxxx]
Sent: Monday, June 21, 2004 1:36 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL Statements in ILE RPG


I wrote a nice little embeded SQL in a RPGLE program.  I tested the 
statement in STRSQL and got 2965 rows returned.  When I run it in my RPGLE I

get 39 rows...

C/exec sql declare c1 cursor for
C+    select cnme, bsdst#, substring(bssdat,1,6), sum(bsexbs)
C+      from bsf100s
C+      join brcm    on bsdst# = rcusno
C+      join rcm     on bsdst# = ccust
C+     where substring(bssdat,1,6) between :strdat and :enddat
C+       and bsrtyp = 'S'
C+     group by cnme, bsdst#, substring(bssdat,1,6)
C+     order by cnme, bsdst#, substring(bssdat,1,6)
C/end-exec

C/exec sql
C+  open c1
C/end-exec

C     sqlcod        doune     0
C/exec sql
C+ FETCH NEXT FROM C1 INTO :icnme, :idst#, :isdat, :iexbs
C/end-exec

I changed doune 0 to doueq 100 and got most of my expected data.  BUT I'm 
still missing some.

The SQLCODE is also set by the database manager after each SQL statement is 
executed as follows:

If SQLCODE = 0 and SQLWARN0 is blank, execution was successful.
If SQLCODE = 100, no data was found. For example, a FETCH statement returned

no data, because the cursor was positioned after the last row of the result 
table.
If SQLCODE > 0 and not = 100, execution was successful with a warning.
If SQLCODE = 0 and SQLWARN0 = 'W', execution was successful with a warning.
If SQLCODE < 0, execution was not successful.
A complete listing of DB2 UDB for iSeries SQLCODEs and their corresponding 
SQLSTATEs is provided in the SQL Messages and Codes book in the iSeries 
Information Center.

I tried reading the "SQL Messages and Codes book" and I think Greek would be

easier to understand.

IF SQLCOD is not equal 0 ang not equal to 100 can I still use the data?

Guy Henza
guyhenza@xxxxxxxxxxx

_________________________________________________________________
MSN Toolbar provides one-click access to Hotmail from any Web page - FREE 
download! http://toolbar.msn.click-url.com/go/onm00200413ave/direct/01/

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.