ORA-00907: missing right parenthesis - Following error is coming when i am putting Case statement in where clause

Question

$ The following code only highlights the where clause of BI Publisher query, in which i have an input paramter :P_COUNTRY, through which i am recieving the values either as PH/MY/CN on the basis of that i have to put the where clause.

WHERE     PRG.PAYROLL_RELATIONSHIP_ID = PEU.PAYROLL_RELATIONSHIP_ID
     AND PEU.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
     AND PEE.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
     AND PRG.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
     AND PPSV.PERSON_ID = PPNF.PERSON_ID
     AND PPSV.PERSON_ID = PAAF.PERSON_ID
     --AND  PPNF.PERSON_ID  = CCPP.PERSON_ID
     AND PAAF.PRIMARY_ASSIGNMENT_FLAG = 'Y'
     AND PAAF.ASSIGNMENT_TYPE = 'E'
     AND PAAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
     AND PPOS.PERSON_ID = PAAF.PERSON_ID
     AND PPOS.LEGAL_ENTITY_ID = PLE.ORGANIZATION_ID
     AND PPNF.NAME_TYPE = 'GLOBAL'
     AND    (CASE   WHEN (:P_Country = 'PH') THEN (UPPER(PET.ELEMENT_NAME) IN ('PHCTC EXT'))
                    WHEN (:P_Country = 'MY') THEN (UPPER(PET.ELEMENT_NAME) IN ('MYCTC EXT'))
                    WHEN (:P_Country = 'CN') THEN (UPPER(PET.ELEMENT_NAME) IN ('CNCTC EXT'))
            END)
     AND PAAF.GRADE_ID = PG.GRADE_ID

It's generally better to use AND/OR instead of case expressions in WHERE clauses etc. — jarlh, Aug 22 '17 at 14:40

score 1 · Accepted Answer · answered Aug 22 '17 at 14:42

What you want would be more like:

WHERE     PRG.PAYROLL_RELATIONSHIP_ID = PEU.PAYROLL_RELATIONSHIP_ID
     AND PEU.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
     AND PEE.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
     AND PRG.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
     AND PPSV.PERSON_ID = PPNF.PERSON_ID
     AND PPSV.PERSON_ID = PAAF.PERSON_ID
     --AND  PPNF.PERSON_ID  = CCPP.PERSON_ID
     AND PAAF.PRIMARY_ASSIGNMENT_FLAG = 'Y'
     AND PAAF.ASSIGNMENT_TYPE = 'E'
     AND PAAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
     AND PPOS.PERSON_ID = PAAF.PERSON_ID
     AND PPOS.LEGAL_ENTITY_ID = PLE.ORGANIZATION_ID
     AND PPNF.NAME_TYPE = 'GLOBAL'
     AND UPPER(PET.ELEMENT_NAME) =
            CASE   WHEN :P_Country = 'PH' THEN 'PHCTC EXT'
                   WHEN :P_Country = 'MY' THEN 'MYCTC EXT'
                   WHEN :P_Country = 'CN' THEN 'CNCTC EXT'
            END
     AND PAAF.GRADE_ID = PG.GRADE_ID

There is no need for all of the parenthesis you were using, and no need for the use of IN when you are only comparing one value.

Another alternative would be to do away with the CASE and use a series of grouped conditional clauses:

WHERE     PRG.PAYROLL_RELATIONSHIP_ID = PEU.PAYROLL_RELATIONSHIP_ID
     AND PEU.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
     AND PEE.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
     AND PRG.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
     AND PPSV.PERSON_ID = PPNF.PERSON_ID
     AND PPSV.PERSON_ID = PAAF.PERSON_ID
     --AND  PPNF.PERSON_ID  = CCPP.PERSON_ID
     AND PAAF.PRIMARY_ASSIGNMENT_FLAG = 'Y'
     AND PAAF.ASSIGNMENT_TYPE = 'E'
     AND PAAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
     AND PPOS.PERSON_ID = PAAF.PERSON_ID
     AND PPOS.LEGAL_ENTITY_ID = PLE.ORGANIZATION_ID
     AND PPNF.NAME_TYPE = 'GLOBAL'
     AND (
        (:P_Country = 'PH' AND UPPER(PET.ELEMENT_NAME) = 'PHCTC EXT') OR
        (:P_Country = 'MY' AND UPPER(PET.ELEMENT_NAME) = 'MYCTC EXT') OR
        (:P_Country = 'CN' AND UPPER(PET.ELEMENT_NAME) = 'CNCTC EXT')
     )
     AND PAAF.GRADE_ID = PG.GRADE_ID

Patrick Bacon · Answer 2 · 2017-08-22T17:02:51.260

Case Expressions are Not Well Suited for the Where Clause

You are using a searched case expression. The results component of the case expression does not accept a boolean.

Here is the existing condition:

   AND    (CASE   WHEN (:P_Country = 'PH') THEN (UPPER(PET.ELEMENT_NAME) IN ('PHCTC EXT'))
                    WHEN (:P_Country = 'MY') THEN (UPPER(PET.ELEMENT_NAME) IN ('MYCTC EXT'))
                    WHEN (:P_Country = 'CN') THEN (UPPER(PET.ELEMENT_NAME) IN ('CNCTC EXT'))
            END)

I translate your case statement in words to the following:

the bind variable, :P_Country, is being checked to see if it is in the PET.ELEMENT_NAME. More so, it is just the first segment of that ELEMENT_NAME.

In SQL, I write:

AND UPPER(PET.ELEMENT_NAME) = :P_Country ||'CTC EXT'

Ideally it would be good to know if ELEMENT_NAME is all upper, lower, proper or mixed. One could modify the case of the bind variable to correspond with the ELEMENT_NAME.

You will get better performance.

for Example if we know ELEMENT_NAME is always UPPER case, we could do this:

AND PET.ELEMENT_NAME = UPPER(:P_Country) ||'CTC EXT'

ORA-00907: missing right parenthesis - Following error is coming when i am putting Case statement in where clause

2 Answers2