Q
Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Creating a host variable of the 'where in' statement in SQL

Create a host variable of the where in statement on the fly with dynamic SQL.

I would like to create the host variable of the where in statement on the fly. Everything I've tried has not worked. Any suggestions?

Example:

SQL                              R040_PRD_GRP_DESC                         
SQL                   from CSSDM01040                                       
SQL       
SQL                   Where R040_PRD_GRP_ROLLUP Not In  " & W-excl & "
SQL                   group by R040_PRD_GRP_ROLLUP    ,                     
SQL                            R040_PRODUCT_GROUP     ,                     
SQL                            R040_PRD_GRP_DESC                           
SQL                   End-Exec.                                             
0090.00    
0091.00               String  '('   Delimited by Size                       
0092.00                        '00'    Delimited by Size                   
0093.00                       ', '   Delimited by Size                     
0094.00                        '27'    Delimited by Size                   
0095.00                       ')'   Delimited by Size                       
0096.00                         Into W-Excl                                 
0097.00  
0099.00 
Creating a host variable of the where in statement on the fly is possible, but it is best implemented with dynamic SQL.

Here's what the psuedo-code would look like:

DECLARE C1 CURSOR FOR S1 ; 

SET W-Excl = '(00,27'); 
SET mystmt = 'SELECT R040_PRD_GRP_ROLLUP,R040_PRODUCT_GROUP,R040_PRD_GRP_DESC,
COUNT (*)  from CSSDM01040   Where R040_PRD_GRP_ROLLUP Not In  ' || W-excl || ' group by 
R040_PRD_GRP_ROLLUP,R040_PRODUCT_GROUP,R040_PRD_GRP_DESC' 

PREPARE S1 FROM mystmt ; 
OPEN C1 ; 
FETCH C1 INTO :rollupvar, :groupvar, :descvar, :countvar ; 
....

Dig Deeper on DB2 UDB (universal databases)

SearchDataCenter

Close