RE: SQL in RPGIV Error Message

Justin,

When you have NULL values returned, you need to provide a set of indicators
for each field returned.  If the field is NULL the indicator for that field
is on.  Here's an example from the SQL Programming with host languages
manual.

DDEPARTMENT DS OCCURS(10)
D DEPTNO 01 03A
D DEPTNM 04 32A
D MGRNO 33 38A
D ADMRD 39 41A
DIND_ARRAY DS OCCURS(10)
D INDS 4B 0 DIM(4)
...
C/EXEC SQL
C+ DECLARE C1 CURSOR FOR
C+ SELECT *
C+ FROM CORPDATA.DEPARTMENT
C/END-EXEC
...
C/EXEC SQL
C+ FETCH C1 FOR 10 ROWS
C+ INTO :DEPARTMENT :IND_ARRAY
C/END-EXEC


Notice there are 4 fields, 10 rows at a time.  So IND_ARRY is 4x10 also.

HTH,
Charles


> -----Original Message-----
> From: Justin Houchin [mailto:justinh@xxxxxxxxxxxxx]
> 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.
>