user3115042 user3115042 - 1 month ago 9
SQL Question

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

$
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

Answer Source

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