ORA-00907: missing right parenthesis

SELECT DISTINCT( EMP.EMPLOYEEID ),
    EMP.EMPLOYEECODE,
    EMP.EMPLOYEENAME,
    EMP.HOMEADDRESS,
    DESIG.DESIGNATIONNAME
  FROM HRM_EMPLOYEE EMP,
    COM_DESIGNATION DESIG,
    COM_DEPARTMENT DEPT,
    COM_COMPANY COMP,
    HRM_EMPLOYEEDEPARTMENTS EMPDEPT,
    USR_USERS USRS
  WHERE EMP.EMPLOYEEID = EMPDEPT.EMPLOYEEID AND
    EMP.DESIGNATIONID  = DESIG.DESIGNATIONID AND
    DESIG.DEPARTMENTID = EMPDEPT.DEPARTMENTID AND
    EMP.STATUS IN  (SELECT  STAT STAT
        FROM
          (
          CASE
          When (:status = 0) THEN
            SELECT   1 STAT FROM dual
            UNION ALL
            SELECT   2 STAT FROM dual
          else
            Select :status STAT from dual
          end
          )
          xx
       ) 

Actually my need is: stow the records according to the parameter passing. if ta开发者_如何转开发t parameter i wish to show all records.

You cannot have a CASE statement as a table expression (unless perhaps if using nested table types). But why so complicated? Instead of this:

EMP.STATUS IN  (SELECT  STAT STAT
    FROM
      (
      CASE
      When (:status = 0) THEN
        SELECT   1 STAT FROM dual
        UNION ALL
        SELECT   2 STAT FROM dual
      else
        Select :status STAT from dual
      end
      )
      xx
   ) 

Write this:

(EMP.STATUS IN (1, 2) AND :status = 0) OR
(EMP.STATUS = :status)

SELECT DISTINCT( EMP.EMPLOYEEID ),
    EMP.EMPLOYEECODE,
    EMP.EMPLOYEENAME,
    EMP.HOMEADDRESS,
    DESIG.DESIGNATIONNAME
  FROM HRM_EMPLOYEE EMP,
    COM_DESIGNATION DESIG,
    COM_DEPARTMENT DEPT,
    COM_COMPANY COMP,
    HRM_EMPLOYEEDEPARTMENTS EMPDEPT,
    USR_USERS USRS
  WHERE EMP.EMPLOYEEID = EMPDEPT.EMPLOYEEID AND
    EMP.DESIGNATIONID  = DESIG.DESIGNATIONID AND
    DESIG.DEPARTMENTID = EMPDEPT.DEPARTMENTID AND
    ( 
     (EMP.STATUS IN (1, 2) and :status = 0)
      or :status <> 0 --This will not filter your status, as I expect you want it to do so
     )