Using variable in SQL WHERE statement

[jlschoen]

I thought I posted this already, in How To but can't find it so I will try again here:

I need to modify the WHERE statement in the following code:

SELECT PSLET_PROJECTID, PSLET_CONTRACTLETDATE, PSLET_OLD_CONTRACTLETDATE,
PSLET_PROJECT_GROUP
FROM S4111000.PSLET_INFORMATION
WHERE PSLET_PROJECTNUMBER = p_INProjectNumber
AND PSLET_DELBYTE IN ('A', 'N', 'H')

I want to create a variable and set the value for PSLET_DELBYTE
depending on who calls this program:

IF p_INProgramNumber = 'S4115019'
THEN
v_sqlLetDelbyte := ('A', 'N', 'H', 'D');
ELSE
v_sqlLetDelbyte := ('A', 'N', 'H');
END IF;

I changed the SQL statement to:

SELECT PSLET_PROJECTID, PSLET_CONTRACTLETDATE, PSLET_OLD_CONTRACTLETDATE,
PSLET_PROJECT_GROUP
FROM S4111000.PSLET_INFORMATION
WHERE PSLET_PROJECTNUMBER = p_INProjectNumber
AND PSLET_DELBYTE IN v_sqlLetDelbyte

The program will not compile because it doesn't like the variable
v_sqlLetDelbyte, but I get in to quotation HELL if I put quotation marks around the whole thing '('A', 'N', 'H', 'D')'.

Dynamic SQL will have the same problem. Is there a way to write
this sql without having to write two separate statements?

Any help will be appreciated!!

Jan

[LKBrwn_DBA]

Try this:

...etc...
AND INSTR(v_sqlLetDelbyte,PSLET_DELBYTE) > 0;

[jlschoen]

I have several places I had to change the code, and it still compiles with your suggested code. Thank you very much!! I will set up the test data and run to make sure I get what I want. But looks good so far.

Again, thank you!

Jan

[slimdave]

How about ...

SELECT PSLET_PROJECTID, PSLET_CONTRACTLETDATE, PSLET_OLD_CONTRACTLETDATE,
PSLET_PROJECT_GROUP
FROM S4111000.PSLET_INFORMATION
WHERE PSLET_PROJECTNUMBER = p_INProjectNumber
AND (PSLET_DELBYTE IN ('A', 'N', 'H') OR (p_INProjectNumber = 'S4115019'
AND PSLET_DELBYTE = 'D'))