[RTL_PRT_PLS].[EY_ID] = [RTL_P_P_STG].[EY_ID]
AND
[RTL_PRT_PLS].[AA_ID] =[CDW_RTL_P_P_STG].[AA_ID]
AND
[RTL_PRT_PLS].[PL_ID] =[RTL_P_P_STG].[PL_ID]
AND
((char([RTL_PRT_PLS].[EXPR_DTE] ) >= CASE WHEN @PROMPT ('Enter Date (format YYYY-MM-DD) or * for Current Date','C',,,) = '*'
THEN '9999-12-31' END )
OR @PROMPT('Enter Date (format YYYY-MM-DD) or * for Current Date','C',,,) BETWEEN char([RTL_PRT_PLS].[EFF_DTE] ) and
char(.[RTL_PRT_PLS].[EXPR_DTE] ))

can anyone able to explain 1) why the '*' is used 2) why there are multiple comms after 'C' and 3) what is that @prompt ?

many thanks in advance...

It is you SQL, you would have to tell us. Where is this coming from? Also what DB2 version and OS?

Andy

All this reminds me of MS access.

What is this "@PROMPT(... , , , )" thing? This is not a valid SQL expression (neither by the accounts of the SQL standard nor DB2).

stolze,

you are right...the problem is with @prompt, which we replaced with #prompt
and removed those multiple commas...
actually i didn't wrote the code...it's a request from the customer !