Oracle SQL conditional operator explanation-CodePudding

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!

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 DUAL

CodePudding user response：

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.