CODEWITHSUNDEEP
sqlcase
Ashlesha
Ashlesha

Reputation: 15

Using select statement inside case in where clause. gettting an error ORA-00905

My sample code.

In case statement inside the where condition, while I'm equating to the variable, I'm getting the error below:

ORA-00905-missing keyword

I do not understand where I went wrong.

I even tried equating complete case statement to the variable, it was not giving the desired output.

Can anyone help me?

SELECT JOB_NAME,
       HOURS,
       COMMITTEE,
       REPORT,
       DIRECTOR,
       (CASE
         WHEN L.ACTION IN ('110', '10') AND
              L.APPROVED = 'Y' AND
              (:LV_ACTN = '1' OR :LV_ACTN = '4') THEN
          L.APPRV_JUSTIFY
         WHEN L.ACTION IN ('20', '130') AND
              L.FLAG = 'R' AND (:LV_ACTN = '2' OR :LV_ACTN = '4') THEN
          L.CANCL_JUSTIFY
         WHEN L.ACTION IN ('30', '120') AND
              L.APPROVED = 'Y' AND
              (:LV_ACTN = '3' OR :LV_ACTN = '4') THEN
          L.POSTPONE_JUSTIFY
         ELSE 
          'NO ACTION' 
       END) AS EXPLANATION,
       L.ID,
       MANAGER,
       l.year
  FROM PLAN L
 WHERE  (CASE WHEN
      L.ACTION IN ('110', '10') AND
              L.APPROVED = 'Y' AND
              (:LV_ACTN = '1' OR :LV_ACTN = '4') THEN 'X' 
         WHEN L.ACTION IN ('20', '130') AND 
              L.FLAG = 'R' AND (:LV_ACTN = '2' OR :LV_ACTN = '4') THEN 'X' 
         WHEN L.ACTION IN ('30', '120') AND
              L.APPROVED = 'Y' AND 
              (:LV_ACTN = '3' OR :LV_ACTN = '4') THEN X' END) IS NOT NULL
   AND  ((case when L.ACTION IN ('30', '120') THEN 
        (SELECT DISTINCT AV.YEAR
            FROM PLAN_V AV
           WHERE L.ID = AV.ID
             AND AV.B_ID =
                 (SELECT MAX(B_ID)
                    FROM MS_AUD_AUDIT_PLAN_V
                   WHERE B_ID <
                         (SELECT MAX(B_ID)
                            FROM PLAN_V
                           WHERE ACTION = '120')))**=:LV_PLANYR** ELSE
         L.YEAR END)**= :LV_PLANYR** )

Upvotes: 0

Views: 683

Answers (1)

Cyril Gandon
Cyril Gandon

Reputation: 17048

I edited your code, and with the syntax highlighter of stackoverflow, the error becomes obvious. You are missing a ' on the line :

(:LV_ACTN = '3' OR :LV_ACTN = '4') THEN X' END) IS NOT NULL

The good line is :

(:LV_ACTN = '3' OR :LV_ACTN = '4') THEN 'X' END) IS NOT NULL

Upvotes: 5

Related Questions