1

I have two tables: pointer_table(col_name varchar, col_val varchar, wh_clause varchar) value_table(col_1 varchar, col_2 varchar, value varchar)

col_name in pointer_table denotes the column in value_table (either col_1 or col_2), while wh_clause contains a complex where clause to be used in the value_table.

When working with col_name and col_val, it's easy to join the two tables eg:

select vt.value from (select col_val from pointer_table where col_name = 'col_1') pt inner join (select col_1, value from value_table) vt on pt.col_val = vt.col_1

However I sometimes need to apply the contents of pointer_table.wh_clause on value_table (wh_clause contains conditions like col_1 = 'pine' and col_2 = 'Argentina'.

Is it possible in DB2 to somehow apply the content of that field as where clause for another table in a query?

3
  • 1
    I don't know much DB2, but I can give you the Google search terms "db2 dynamic sql"
    – Philᵀᴹ
    Commented Feb 4, 2015 at 11:42
  • Thank you. I regret to find that dynamic sql is most probably only usable/supported within procedures instead of "normal" command line queries. Commented Feb 4, 2015 at 12:26
  • @Phil can you please add your comment as an answer? I'd like to upvote and accept it. Commented Feb 5, 2015 at 13:54

1 Answer 1

2

If you implement the process as a stored procedure, it can generate the dynamic SQL based on the rules in your base tables and then execute that dynamic statement.

From the DB2 CLP, it's possible to write a query (probably containing one or more CTEs) that generates the SQL. The EXPORT utility can write the results of that query to a text file whose name is already in your master script's list of files to execute.

Another option is CLPPlus, which IBM based on SQLPlus from Oracle. It has some capabilities to iteratively construct dynamic SQL in a scratchpad-like area and then execute it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.