Re: sql saga continues

Douglas,

this may not be relevant here, but I always use a 15,0 host variable for a 
count(*).

>From the SQL reference:

"The COUNT function returns the number of rows or values in a set of rows 
or values. expression The argument values can be of any built-in data type 
other than a BLOB, CLOB, DBCLOB, or DataLink. If DISTINCT is used, the 
resulting expression must not have a length attribute greater than 2000 
for a character column or 1000 for a graphic column. The result of the 
function is a large integer and it must be within the range of large 
integers. The result cannot be null. If the table is a distributed table, 
then the result is DECIMAL(15,0). For more information about distributed 
tables, see the DB2 Multisystem book."

Maybe your result is getting truncated?

HTH,

Peter Colpaert
Application Developer
Massive - Kontich, Belgium
-----
Yoda of Borg are we.  Futile is resistance, assimilated will you be.
-----




"Douglas W. Palme" <dpalme@xxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
14/07/2005 16:04
Please respond to RPG programming on the AS400 / iSeries

 
        To:     "RPG Group" <rpg400-l@xxxxxxxxxxxx>
        cc: 
        Subject:        sql saga continues


I spent the better portion of the night last night continuing to work with 

the embedded sql code.....of course its a learning curve and at times 
frustrating as hell but I am learning it slowly....


I have however run into a situation that after 4 hours I have not been 
able 
to figure out.  I have created a very simple sqlrpgle program that for 
right 
now is just to get the count of the records and write it to a variable. 
Sounds simple enough, however when running it in bebug mode it seems to 
execute but when I F11 the variable to see its value, its all zeroes.

Here is the pertinent code:

0029.03  ***********************************************************
0039.01  *       STANDALONE VARIABLES 
0041.00  ***********************************************************
0041.03 DSDATE            S               D   DATFMT(*USA) 
0041.04 DEDATE            S               D   DATFMT(*USA) 
0041.05 DTCOUNT           S              6S 0 
0041.06  ***********************************************************
0042.00  *       MAINLINE 
0043.00  ***********************************************************
0043.01 C     *USA          MOVE      '06/01/2005'  SDATE 
0043.02 C     *USA          MOVE      '06/30/2005'  EDATE 
0043.03 C                   EXSR      COUNTING 
0043.29 C                   EVAL      *INLR = *ON 
0043.30 C                   RETURN 
0043.31  ***********************************************************
0044.00  *       GET RECORD COUNT AND WRITE TOTAL TO TCOUNT VARIABLE
0045.00 C     COUNTING      BEGSR 
0046.00 C/EXEC SQL 
0047.00 C+    DECLARE COUNTCURSOR CURSOR
0048.00 C+    FOR SELECT COUNT(*) FROM 
0049.00 C+    INVMARGIN WHERE 
0050.00 C+    ILNINVDATE >= :SDATE AND 
0051.00 C+    ILNINVDATE <= :EDATE 
0052.00 C/END-EXEC 
0053.00 C/EXEC SQL 
0054.00 C+ FETCH COUNTCURSOR 
0055.00 C+ INTO :TCOUNT 
0056.00 C/END-EXEC 
0057.00 C                   ENDSR
0058.00  ************************

I did not see any problem with the cursor or the fetch statement, and 
since 
the variable tcount is declared it should work....I also noticed when I 
F11 
on the :tcount it says that it cannot find the variable...which I thought 
was strange but maybe its my brain having one of its moments.

Any helpful suggestions would be appreciated.