0

enter image description here

Can anyone help me?

I want to use query using CASE WHEN with logic where time less than 03:00 PM on Monday until Friday will get value start from tomorrow 03:00 PM until today 03:00 PM

  SELECT SUBSTR (A.TXT_TXN_DESC, 7, 15)                  AS billing_no,
         TO_CHAR (dat_txn, 'dd/mm/yyyy hh24:mi:ss.')     AS waktu,
         ''                                              AS note
    FROM ncbshost.v_ch_nobook A
   WHERE     cod_drcr = 'C'
         AND (CASE
                  WHEN (SELECT TO_CHAR (SYSDATE, 'dd/mm/yyyy hh24:mi:ss')
                          FROM DUAL) <=
                       TO_CHAR (SYSDATE, 'dd/mm/yyyy') || ' 15:00:00'
                  THEN
                      dat_txn BETWEEN (CASE
                                           WHEN (SELECT TRIM (
                                                            TO_CHAR (SYSDATE,
                                                                     'DAY'))
                                                   FROM DUAL) =
                                                'MONDAY'
                                           THEN
                                               TO_DATE (
                                                      TO_CHAR (SYSDATE - 3,
                                                               'dd/mm/yyyy')
                                                   || ' 15:00:00',
                                                   'dd/mm/yyyy hh24:mi:ss')
                                           ELSE
                                               TO_DATE (
                                                      TO_CHAR (SYSDATE - 1,
                                                               'dd/mm/yyyy')
                                                   || ' 15:00:00',
                                                   'dd/mm/yyyy hh24:mi:ss')
                                       END)
                                  AND TO_DATE (
                                             TO_CHAR (SYSDATE, 'dd/mm/yyyy')
                                          || ' 15:00:00',
                                          'dd/mm/yyyy hh24:mi:ss')
                  ELSE
                      a.dat_txn BETWEEN TO_DATE (
                                               TO_CHAR (SYSDATE, 'dd/mm/yyyy')
                                            || ' 15:00:00',
                                            'dd/mm/yyyy hh24:mi:ss')
                                    AND TO_DATE (
                                               TO_CHAR (SYSDATE, 'dd/mm/yyyy')
                                            || ' 23:59:59',
                                            'dd/mm/yyyy hh24:mi:ss')
              END)
ORDER BY SUBSTR (A.TXT_TXN_DESC, 7, 15) ASC
  • 1
    You are missing a keyword. Refer the Oracle manual for the correct syntax. – 48347 Nov 4 at 9:20
  • You are doing the comparison inside the case statement, which isn't supported by Oracle. You need to do dat_txn between case .... end and case ... end – Boneist Nov 4 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.