Adding DB2 dynamic SQL code within an existing where clause

Question

I have a query which runs for orders placed in the last hour. Depending on the client, certain conditions are applied to the WHERE clause of the query. The SQL WHERE Clause is stored in a separate table for each client.

The query would look like this:

SELECT * FROM ORDERS
WHERE <INSERT SQL STATEMEMT>

I created a FUNCTION to extract the SQL Statement from the table.

CREATE OR REPLACE FUNCTION QUERY_SQL( 
iCLIENTID VARCHAR(10)) 
RETURNS VARCHAR(4000)
LANGUAGE SQL 
SPECIFIC FN_QUERY_SQL
READS SQL DATA 
NO EXTERNAL ACTION 
DETERMINISTIC 
BEGIN ATOMIC 

DECLARE SQLQUERY VARCHAR(4000);

SET SQLQUERY=(SELECT CAST(SQL_STMT AS VARCHAR(4000)) 
FROM CONSUMER_NOTIFICATION 
            WHERE 
    CLIENT_ID=iCLIENTID
    FETCH FIRST ROW ONLY
    );
  RETURN SQLQUERY; 

  END

I'm having a problem just inserting the code into the WHERE clause. I've tried to PREPARE the SQL Code but that does not seem to work as the SQL is only a WHERE clause not a complete query.

The DB2 dynamic SQL I've seen generates based on parameter values where the SQL state is fairly fixed. I need to change the whole WHERE clause per client.

Thanks for the help in advanced.

score 0 · Accepted Answer · answered Jun 04 '21 at 12:45

You haven't described how you use the final statement exactly.
Briefly: you can't use a parameter marker for whole expression in WHERE.
Look at the example below.

--#SET TERMINATOR @

CREATE OR REPLACE PROCEDURE TEST_WHERE (P_WHERE VARCHAR(500)) 
DYNAMIC RESULT SETS 1
BEGIN
  DECLARE C1 CURSOR WITH RETURN FOR S1;

  PREPARE S1 FROM 'SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE ' || P_WHERE;  
  OPEN C1;

  -- Don't try something like below
  -- It will expect a BOOLEAN parameter there, but it's proabably not what you need:
  -- call test_where('FALSE') / call test_where('TRUE')

  -- PREPARE S1 FROM 'SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE ?';
  -- OPEN C1 USING P_WHERE;
END
@

call test_where('TABSCHEMA = ''SYSCAT'' FETCH FIRST 5 ROWS ONLY')@

So I have to generate the whole query as as dynamic SQL instead of just being able to insert the WHERE clause dynamically, Correct? — Cypherwolf, Jun 04 '21 at 13:01
You use not correct wording. 1. You must use dynamic SQL (and not static one, when access plan for your statement is generated at the routine compilation time). 2. You must not use parameter makers in this dynamic statement. — Mark Barinstein, Jun 04 '21 at 14:11

Adding DB2 dynamic SQL code within an existing where clause

1 Answers1