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?

  • 1
    I don't know much DB2, but I can give you the Google search terms "db2 dynamic sql" – Philᵀᴹ Feb 4 '15 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. – András Hummer Feb 4 '15 at 12:26
  • @Phil can you please add your comment as an answer? I'd like to upvote and accept it. – András Hummer Feb 5 '15 at 13:54
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, privacy policy and cookie policy

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