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 ; 
....
This was last published in June 2009

Dig Deeper on DB2 UDB (universal databases)

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

I need to create a log of database changes and evaluate the before and after images and parse out which field changed on a the record, etc. My files are journaled. I have the utility to extract the journal data. My current question is do we have a DB2 Audit function on Iseries that will readily give me this comparison data or will I have to write this utility to parse out which fields on the record changed? I don't want to reinvent the wheel if it already exists.
Cancel

-ADS BY GOOGLE

SearchDataCenter

Close