RE: SQL in RPGIV Error Message

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

Charles,

As usual, I've already exceeded the limits of my practical knowledge of null
support.  As I said, I generally prefer to use the coalesce function, which
eliminates the null issue.  I haven't had a need to learn more about null
support....  


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



-----Original Message-----
From: CWilt@xxxxxxxxxxxx [mailto:CWilt@xxxxxxxxxxxx]
Sent: Friday, April 30, 2004 9:40 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: SQL in RPGIV Error Message


Eric,

You're the second person to mention ALWNULL.  I was under the impression
that this keyword affects only RPG native processing.  Since the original
poster was using embedded SQL, he needed to include a manually defined NULL
indicator array.

If ALWNULL(*USRCTL) was specified does RPG automatically track and pass the
NULL indicators?


Thanks,
Charles


> -----Original Message-----
> From: DeLong, Eric [mailto:EDeLong@xxxxxxxxxxxxxxx]
> Sent: Friday, April 30, 2004 10:27 AM
> To: 'RPG programming on the AS400 / iSeries'
> Subject: RE: SQL in RPGIV Error Message
> 
> 
> 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.
> 
> 
> _______________________________________________
> 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.
> 
_______________________________________________
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.