How to include a where clause in a query from another column

Question

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?

Answer 1

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.