RE: SQL in RPGIV Error Message -- RPG400-L

Justin, 

You're correct that the null value is causing the problem.  Since null is an
attribute of the field and not a value, you need to either 1) enable the
program to ALWNULL and incorporate the additional logic to support null
field detection, or 2) use the coalesce() function to convert the null to a
default value such as zero.  

I think coalesce would normally be my preference...

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



-----Original Message-----
From: Justin Houchin [mailto:justinh@xxxxxxxxxxxxx]
Sent: Friday, April 30, 2004 6: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.