Dealing with NULL on the SQLRPGLE side

[mjhaston]

I may have "out-smart" myself this time! Most of the time I develop my SQL statements in Ops Nav to get the data I need and then either cut & paste the code into static SQL in my program or pull it apart to build dynamic SQL.

This time I pretty much left the SQL as-is and used an Aaron Bartell procedure to read the IFS .sql file into an RPG variable and use that in my prepare statement.

Works great. Had to do a find/replace on the :host variable, but still okay.

with x as (

select 
 affiliate,
 regiondesc,
 state,  
 gender,
 case when compensate between 1 and 30000 then 1 else 0 end as a,
 case when compensate between 30001 and 40000 then 1 else 0 end as b,
 case when compensate between 40001 and 50000 then 1 else 0 end as c,
 case when compensate > 50000 then 1 else 0 end as d

from file

)

select 
 affiliate,
 case when :p_sort = 'R' then regiondesc else null end,
 state, 
 gender,
 sum(a) as less_than_30k,
 sum(b) as between_30k_40k,
 sum(c) as between_40k_50k,
 sum(d) as greater_than_50k

from x

where
 case when :p_entity = 'ALL' then affiliate else :p_entity end = affiliate

group by  
 affiliate, 
 case when :p_sort = 'R' then regiondesc else null end,
 state, 
 gender

order by  
 affiliate, 
 case when :p_sort = 'R' then regiondesc else null end,
 state, 
 gender

Here is the problem I'm facing. Normally when I do dynamic SQL and have a varying number of host variables I do something like this:

if ( p_sort = 'R' );                                                   
  exec sql  fetch c1 into :affiliate, [B][I]:regionDesc,[/I][/B] :stateDesc, :gender,
                          :bucketA, :bucketB, :bucketC, :bucketD       
  ;                                                                    
else;                                                                  
  exec sql  fetch c1 into :affiliate, :stateDesc, :gender,             
                          :bucketA, :bucketB, :bucketC, :bucketD       
  ;                                                                    
endif;

Notice :regionDesc is returned in one fetch, but not the other.

With my new SQL statement above regionDesc is either returns a real value or null if regionDesc is not part of the select and grouping clauses. I'm not sure how to handle this null in RPG. My DDS is defined as ALWNULL for the regionDesc field.


Here is how my data is returned if by State:

MMB	null	AL	F	37	8	2	1	
MMB	null	AL	M	25	6	6	5	
MMB	null	AR	F	1	0	0	0	
MMB	null	AR	M	4	0	0	1

[GLS400]

Re: Dealing with NULL on the SQLRPGLE side

Hi mjhaston:

Why use Null?
Can't you use ' ' in place of the null?

Best of Luck
GLS

[mjhaston]

Re: Dealing with NULL on the SQLRPGLE side

Hi mjhaston:

Why use Null?
Can't you use ' ' in place of the null?

Best of Luck
GLS

Well of course I can! But that would have been too easy from the start. Thanks for the help. I tried that before and got a different error message and threw my hands up in the air. Now I realize it was because I wasn't providing the host variable for the regiondesc when I was testing that.

Runs perfectly! Thanks GLS.

[Scott Klement]

Re: Dealing with NULL on the SQLRPGLE side

Can 'regionDesc' ever contain an empty string? If so, if you return '' from your SQL statement, you won't be able to distinguish between the empty string from the database, and the empty string that's a result of _Sort='R'. (But, maybe that's not a problem in your application.)

But, if you do need to distinguish, or if you're just curious.... you can code a null indicator on the fetch statement for that field.

D nullRegion      s              5i 0
  .
  .
  exec sql  fetch c1 into :affiliate, :regionDesc :nullRegion, :stateDesc, :gender,
                          :bucketA, :bucketB, :bucketC, :bucketD       
  if nullRegion = -1;
     //  regionDesc was null
  endif;

Note that there is no comma between :regionDesc and :nullRegion. When you specify two variables this way without a comma between them, the second one is a null indicator, and will contain -1 if the first field is null, or 0 otherwise. nullIndicators should always be 5i 0 (the older 2b 0 also works, but isn't recommended.)

[gcraill]

Re: Dealing with NULL on the SQLRPGLE side

I didn't know you could do that Scott - Nice example thanks!

[mjhaston]

Re: Dealing with NULL on the SQLRPGLE side

I've glimpsed at using null indicator, probably seen their suggested use in error messages, but never used them. I am curious so I'll play around with your example. Thanks Scott, hope you're in good health these days.