Please, may I ask if anyone can explain more on the following oracle sql conditional operator please ? I only have limited oracle sql knowledge that it exclude checking for Sat and sometime for Sunday. Thanks so much!
x
SELECT DECODE(RTRIM(TO_CHAR(SYSDATE, 'DAY')), 'SATURDAY', 4, 'SUNDAY', DECODE(SIGN(TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) - 20), 0, 4, -1, 4, COUNT(*)), COUNT(*)) FROM DUALAdvertisement
Answer
The decode is basically checking the day of the week and returns some magic number = 4, which seems to be some enumerator for status. Anyway, to add the Friday after 11 pm will be:
SELECT CASE WHEN TO_CHAR(SYSDATE,'DY') = 'FRI' AND EXTRACT(HOUR FROM CAST(SYSDATE AS TIMESTAMP)) >= 23 THEN 4 WHEN TO_CHAR(SYSDATE,'DY') = 'SAT' THEN 4 WHEN TO_CHAR(SYSDATE,'DY') = 'SUN' AND EXTRACT(HOUR FROM CAST(SYSDATE AS TIMESTAMP)) <= 20 THEN 4 ELSE COUNT(*) END FROM dual;P.S. if your decode is 3 lines, you probably need to replace it with CASE. Case can do anything the DECODE can and is more readable.