RE: SQL in RPGIV Error Message

["Reeve" <news@xxxxxxxxxx>]

It means you referred to a column from a joined file where there wasn't a
match.  You said "left join", not "left outer join", so YOU have to handle
the exceptions (when no joined row is found).  It's like you're referring to
a parameter that doesn't exist, so SQL says it's null.

Use COALESCE to plug it a default value or add indicator variables to the
WFP columns.

Regards,
Reeve Fritchman
Ayers Rock Software LLC
4915 S. West Shore Boulevard
Tampa, Florida 33611-3329
(813) 831-8574 (voice)
(813) 832-6391 (fax)

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-
> bounces@xxxxxxxxxxxx] On Behalf Of Justin Houchin
> Sent: Friday, April 30, 2004 7:34 AM
> To: 'RPG programming on the AS400 / iSeries'
> Subject: SQL in RPGIV Error Message
> 
> Hi Everyone,
>     I have the following SQL Statement executing in a RPGIV program.
> Through debugging the SQL return codes, I receive the following messages
> once the statement executes:
> 
> SQLCOD = -305
> SQLSTT = 22002
> 
> The SQLSTT error code 22002 means this: "A null value, or absence of an
> indicator parameter was detected; for example, the null value cannot be
> assigned to a host variable, because no indicator variable is specified".
> 
> Here is the datastructure I am reading the statement into:
>      D Hardware        DS
>      D  LnPart#
> 15
>      D  LnWh
> 2
>      D  LnInvType
> 1
>      D  LnDesc
> 30
>      D  LnQty                           9
> 2
>      D  LnPackSep
> 2
> Note: It is possible for LnPackSep to get a null value, maybe this is
> the problem????
> 
> Here is the SQL statement:
> 
> SELECT PMP.PMPART, M3P.M3WH, ISP.ISITYP, PMP.PMDESC, WFP.WFVAL,
> SUM(M3P.ML3RQ) AS REQ_QTY
> FROM M3P, PMP, ISP, O6PWO
> LEFT JOIN WFP ON (WFP.WFSEGN = 'D.COLOR') AND
>                  (WFP.WFORD# = O6PWO.O6ORD#) AND
>                  (WFP.WFSUFX = O6PWO.O6SUFX) AND
>                  (WFP.WFITEM = O6PWO.O6ITEM)
> WHERE (M3P.M3PN=PMP.PMPART) AND
> (CHAR(M3P.M3ON) = O6PWO.O6WO#) AND
> (M3P.M3PN = ISP.ISPART) AND
> ((O6PWO.O6ORD# = 1023 AND O6PWO.O6SUFX=0) OR
> (O6PWO.O6ORD# = 1025 AND O6PWO.O6SUFX = 0)) AND
> ISP.ISWH = M3P.M3WH
> GROUP BY PMP.PMPART, M3P.M3WH, ISP.ISITYP, PMP.PMDESC, WFP.WFVAL
> ORDER BY M3P.M3WH, ISP.ISITYP,PMP.PMPART
> 
> The program worked fine until I added the 4 lines of SQL statement to
> left join the WFP file.  Also the above statement works correctly using
> the interactive SQL utility (STRSQL).  I have a feeling the problem has
> to do with the LnPackShp variable receiveing a null value, but I am not
> for sure and do not know how to correct the problem. Any help would much
> be appreciated.
> 
> Thanks.
> Justin Houchin
> 
> _______________________________________________
> 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.