Why I am getting '[Error] Execution (48: 50): ORA-00936: missing expression'-CodePudding

      SELECT CASE
            WHEN(SELECT TO_CHAR(A.BILL_DT, 'MM') )  >= 4
            THEN
               CONCAT (SELECT TO_CHAR(A.BILL_DT, 'YYYY') ),(SELECT TO_CHAR(A.BILL_DT 1, 'YYYY')  )
            ELSE
               CONCAT (SELECT TO_CHAR(A.BILL_DT, 'YYYY')  ) - 1, (SELECT TO_CHAR(A.BILL_DT, 'YYYY')   ))
         END AS FY,
         SUM (A.BILL_AMT)
    FROM REVADMIN.REV_BILL_HEADER A
GROUP BY CASE
            WHEN (SELECT TO_CHAR(A.BILL_DT, 'MM')  ) >= 4
            THEN
               CONCAT(SELECT TO_CHAR(A.BILL_DT, 'YYYY')), (SELECT TO_CHAR(A.BILL_DT, 'YYYY'))   1)
            ELSE
               CONCAT (SELECT TO_CHAR(A.BILL_DT, 'YYYY' )) - 1, (SELECT TO_CHAR(A.BILL_DT, 'YYYY' ))
         END;

This supposed to be true but still getting error why am getting missing expression error

CodePudding user response：

Remove SELECT from CASE expression; just use column from the table in FROM clause.

For simplicity, I also removed concat and used the double pipe || concatenation operator.

(Though, I'm not sure what is that piece of code supposed to return, doesn't make much sense to me but I guess you know.)

  SELECT CASE
            WHEN TO_CHAR (A.BILL_DT, 'MM') >= 4
            THEN
               TO_CHAR (A.BILL_DT, 'YYYY') || TO_CHAR (A.BILL_DT   1, 'YYYY')
            ELSE
               TO_CHAR (A.BILL_DT, 'YYYY') - 1 || TO_CHAR (A.BILL_DT, 'YYYY')
         END AS FY,
         SUM (A.BILL_AMT)
    FROM REVADMIN.REV_BILL_HEADER A
GROUP BY CASE
            WHEN TO_CHAR (A.BILL_DT, 'MM') >= 4
            THEN
               TO_CHAR (A.BILL_DT, 'YYYY') || TO_CHAR (A.BILL_DT   1, 'YYYY')
            ELSE
               TO_CHAR (A.BILL_DT, 'YYYY') - 1 || TO_CHAR (A.BILL_DT, 'YYYY')
         END

CodePudding user response：

To me it seems the 'SELECT's right before your column expressions shouldn't be there, like

WHEN( TO_CHAR(A.BILL_DT, 'MM') )  >= 4

instead of

WHEN(SELECT TO_CHAR(A.BILL_DT, 'MM') )  >= 4

(For that case, I'd still be wondering whether your to_char() will be correctly compared to a number value.)