Comparing dates in DECODE function (Oracle)

[Budjette]

Hi,

I want to create a decode function that compare dates, NOTES.DATE_CHANGED and SYSDATE. I want it to look like this:
DECODE(TO_DATE(TO_CHAR(NOTES.DATE_CHANGED, 'MM-DD-YYYY'), 'MM-DD-YYYY') >= NEXT_DAY(TO_DATE(TO_CHAR(SYSDATE, 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'MONDAY')-14, null, 'Yes', 'Non'). But I get the message "missing right parenthesis" before the sign ">". Can somebody give me a hand on this one???

Thank you,

Anne-Marie

 

[Naith]

Anne-Marie,

You've surprised the Oracle by putting a relational operand in the decode. Decode is just an equality verifier, it won't do >,<.

I haven't tested this, but you might be able to go with

SELECT CASE
WHEN TO_DATE(TO_CHAR(NOTES.DATE_CHANGED, 'MM-DD-YYYY'), 'MM-DD-YYYY') >= NEXT_DAY(TO_DATE(TO_CHAR(SYSDATE, 'MM-DD-YYYY'), 'MM-DD-YYYY')...

Naith

 

[jee]

I don't think what you are trying to do will work. Oracle expects an expression as the first parameter to DECODE and what you have is a condition. You can use a CASE statement:

SELECT CASE WHEN TO_DATE(TO_CHAR(DATE_CHANGED, 'MM-DD-YYYY'), 'MM-DD-YYYY') >= NEXT_DAY(TO_DATE(TO_CHAR(SYSDATE, 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'MONDAY')-14 THEN 'YES' ELSE 'NON' END
FROM NOTE
WHERE whatever;
[\code]
except I don't think this works within PL/SQL.

Another thing you could do is compare the two dates with a LEAST or GREATEST function within a DECODE. Something like:
[code]
SELECT DECODE (LEAST (TO_DATE(TO_CHAR(DATE_CHANGED, 'MM-DD-YYYY'), 'MM-DD-YYYY'), NEXT_DAY(TO_DATE(TO_CHAR(SYSDATE, 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'MONDAY')-14 ),TO_DATE(TO_CHAR(DATE_CHANGED, 'MM-DD-YYYY'), 'MM-DD-YYYY')  'YES', 'NO' )
FROM NOTE
WHERE whatever
[\code]