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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

SearchEnterpriseLinux

SearchDataCenter

Close