RE: SQL Statements needed? and checking statements

Most likely the problem is with your host variables.  Where you have 
pskdl1=':pcd' what happens is the value of pskdl1 is checked against the 
character string :plcd & not the value of :plcd

Also where you have pskdl2=':shortyy:cmm'  the same issue exists.  My question 
is that it appears you wish to check to see if pskdl2 is equal to the 
concatenated values of :shortyy & :cmm  if this is the case you will have to 
either concatenate the values into a combined field & use the combined field OR 
use concat(:shortyy, :cmm)

     C/Exec SQL

     C+ INSERT INTO sswdpml (mplcd, mmyear, mmdl, mtyp, mext, mint,
units)

     C+ SELECT psplcd, psyear, psmdl, pstyp, psexcl, psinbt,

     C+ sum(psuqty) as "UNITS" FROM sst25 WHERE pskdl1=:plcd

     C+ AND pskdl2 =concat(:shortyy , :cmm )

     C+ AND INT(pskdl3) > INT(:LISNUM)

     C+ GROUP BY psplcd, psyear, psmdl, pstyp, psexcl, psinbt

     C/End-exec

HTH....

Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Brian Piotrowski
Sent: Thursday, June 09, 2005 1:56 PM
To: RPG programming on the AS400 / iSeries
Subject: SQL Statements needed? and checking statements


Hi All,

 

I'm here to ask some more SQL questions! :-)

 

Thanks to the group's help, I was able to get my SQL to work as
expected.  However, now I'm going through the code and trying to
streamline it to make it more effective.

 

However, I may have streamlined it too much, as my second SQL will not
run.

 

Here's the code I have so far:

 

     D  LISNUM         S              3A   INZ

     D  Plant          S              7A   INZ

     D  SRISN          S              3A   INZ

     D  SQLgetlin      S            500A   INZ varying

     D  SQLmlist       S            500A   INZ varying

     D  apos           C                   ''''

     D  cyy            S              4A   INZ

     D  cmm            S              2A   INZ

     D  psyear         S              1A   INZ

     D  psmdl          S              3A   INZ

     D  pstyp          S              3A   INZ

     D  psexcl         S             11A   INZ

     D  psinbt         S              2A   INZ

     D  units          S              6S 0 INZ

     D  shortyy        S              2A   INZ

     D

     D*

     C*<PARAMETERS>

     C     *ENTRY        PLIST

     C                   PARM                    PLCD              1

     C*

     C*----------------------------------------------------------------

     C*---                      Main Routine                        ---

     C*----------------------------------------------------------------

     C*

     C*

     C* Get the last issue number that has S6 shipped

     C*

     C*

      /free

       cyy = %char(%subdt(%date():*Y));

       if %len(%trim(%char(%subdt(%date():*M)))) = 1;

         cmm = '0' + %char(%subdt(%date():*M));

       else;

         cmm = %char(%subdt(%date():*M));

       endif;

       SQLgetlin = 'SELECT MAX(srisn3) FROM SST22';

       SQLgetlin = SQLgetlin + ' WHERE srisn0 = ' + apos + PLCD + apos;

       SQLgetlin = SQLgetlin + ' AND srisn1 = ' + apos + cyy + cmm +
apos;

       SQLgetlin = SQLgetlin + ' AND srisn2 = ' + apos + 'PS' + apos;

       if plcd = '1';

         SQLgetlin = SQLgetlin + ' AND srtlcd=' + apos + 'S6' + apos;

       else;

         SQLgetlin = SQLgetlin + ' AND srtlcd=' + apos + 'S06' + apos;

       endif;

       SQLgetlin = SQLgetlin + ' AND sradnf =' + apos + '1' + apos;

      /end-free

 

     C/Exec SQL

     C+ PREPARE SQL FROM :SQLgetlin

     C/End-exec

     C*

     C/Exec SQL

     C+ DECLARE C1 CURSOR for SQL

     C/End-exec

     C*

     C/Exec SQL

     C+ OPEN C1

     C/End-exec

     C*

     C/Exec SQL

     C+ Fetch C1 into :LISNUM

     C/End-exec

     C*

     C/Exec SQL

     C+ Close C1

     C/End-exec

     C*

 

      /free

         shortyy = %subst(cyy:3:2);

      /end-free

 

     C/Exec SQL

     C+ INSERT INTO sswdpml (mplcd, mmyear, mmdl, mtyp, mext, mint,
units)

     C+ SELECT psplcd, psyear, psmdl, pstyp, psexcl, psinbt,

     C+ sum(psuqty) as "UNITS" FROM sst25 WHERE pskdl1=':plcd'

     C+ AND pskdl2 =':shortyy:cmm'

     C+ AND INT(pskdl3) > INT(:LISNUM)

     C+ GROUP BY psplcd, psyear, psmdl, pstyp, psexcl, psinbt

     C/End-exec

     C*

     C*

      /free

 

       *InLr = *on;

      /end-free

And here's what the program is supposed to do:

1) The system will check table SST25 for the highest issue number
shipped.

2) Based on this number, the system will populate a table with this SQL
(this is actual code I have tried in the SQL interpreter, and has worked
successfully):

 

INSERT INTO sswdpml (mplcd, mmyear, mmdl, mtyp, mext, mint, units)
SELECT psplcd, psyear, psmdl, pstyp, psexcl, psinbt,        

sum(psuqty) as "UNITS" FROM sst25 WHERE pskdl1 ='1' AND pskdl2='0506'
AND INT(pskdl3) > INT(057) GROUP BY psplcd, psyear, psmdl, 

pstyp, psexcl, psinbt

 

Now I know that the system is returning a value when the first SQL
statement is run, so I tried to streamline the second bit of code by
directly including the SQL statement without the CURSOR, PREPARE,
DELCARE, FETCH, etc. statements.  However, it didn't work.

 

My first question is:  Are all of those SQL statements necessary
(PREPARE, DELCARE, etc.) if I am retrieving a value from a table and
passing it as a variable to the next SQL statement without displaying it
to the user?

 

My second question is: Is there a way I can force the system to show me
what is happening when I run the program?  I tried to put a breakpoint
on the code, but when I run it in debug mode it didn't provide any
useful information.

 

I guess my final question would be is there an easier way to accomplish
what I am trying to do in this code?

 

Thanks again, group!

 

Brian.

 

 

-=-=-=-=-=-=-=-=-=-=-=-=-=-

Brian Piotrowski

Specialist - I.T.

Simcoe Parts Service, Inc.

Ph: 705-435-7814 x343

Fx: 705-435-6746

bpiotrowski@xxxxxxxxxxxxxxx

-=-=-=-=-=-=-=-=-=-=-=-=-=-