RE: RPG SQL Question

[rob@xxxxxxxxx]

Terri,

What is your Query product?  We use Query/400.  With that you can
'decompile' the query using RTVQMQRY and see the underlying SQL statement
that is created.

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin



                    THarteau@felkerbr
                    others.com              To:     rpg400-l@midrange.com
                    Sent by:                cc:
                    rpg400-l-admin@mi       Fax to:
                    drange.com              Subject:     RE: RPG SQL Question


                    01/28/2002 10:52
                    AM
                    Please respond to
                    rpg400-l







Hi,
     Thanks for all the suggestions. I tried most of them, with mixed
results.
I can get the SQL to work properly in our query product, but I can't see
the statement it generates.
If I take out the secondary file, all the records print properly.
I added the check for null, and I got the previous line repeated instead of
the correct line.

Because of time, I decided to take out the left outer join, and just do a
chain to that file. Now it won't compile. This is code I am using:
C/EXEC SQL DECLARE A CURSOR FOR
C+  SELECT FPROD, FPBUYC, FQTY, FRDTE, FDATE
C+  FROM KFPL01
C+  WHERE FTYPE = 'F' AND FPBUYC IN ('G', 'F')
C+  ORDER BY FPROD
C/END-EXEC
C/EXEC SQL
C+  OPEN A
C/END-EXEC
C*
C                   DOU       MORREC = 'NO '
C/EXEC SQL
C+ FETCH A INTO :FPROD, :FPBUYC, :FQTY, :FRDTE, :FDATE
C/END-EXEC
C*
Error checking stuff
C     FPROD         CHAIN     ECLL04                             60

The error I get is *RNF7055 30    274 004700  Factor 1 FPROD is not valid
for the specified operation;
                           the specification is ignored.
FPROD is the field name in KFP. When I do the fetch, do I need to put it
into a field with a different name? FPROD is defined as 15A, the same as
the first key field in ECLL04.  I know I don't know much about SQL, but
this seams like it should be a no-brainer. Help


<===================================================>

Terri Harteau
****************
"There's no point in being grown up if you can't be childish sometimes."
- Dr. Who
****************

Original Post:
     I have this SQL statement in my program:

C/EXEC SQL DECLARE A CURSOR FOR
C+  SELECT FPROD, FPBUYC, FQTY, FRDTE, FDATE, LORD, LLINE, LRDTE, LSDTE
C+  FROM KFPL01
C+  LEFT OUTER JOIN
C+    ECLL01 ON LPROD = FPROD
C+  WHERE FTYPE = 'F' AND FPBUYC IN ('G', 'F')
C+    AND FRDTE > LRDTE
C+  ORDER BY FPROD
C/END-EXEC

I want to print all records that meet the criteria from KFP, and the
associated ECL information. There is one KFP record that has no associated
ECL record.  This record is not printing.  Both KFPL01 & ECLL01 are logical
files that contain only active records. The missing ECL record is coded as
inactive, so is in the file, but shouldn't be found using the logical. I've
never used the outer join before, but from what I have read, I thought this
would work. Any ideas on what I am doing wrong? Thanks!





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