ORA-00907: 缺少右括号

发布于 2024-10-27 15:11:56 字数 816 浏览 1 评论 0原文

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
       )

其实我的需求是：根据参数传递来存放记录。如果 tat 参数我希望显示所有记录。

原文

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 tat parameter i wish to show all records.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

坏尐絯℡ 2024-11-03 15:11:56

您不能将 CASE 语句用作表表达式（除非使用嵌套表类型）。但为什么这么复杂呢？而不是这样写：

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
   )

写这样：

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

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)

回复收藏 0 原文

天赋异禀 2024-11-03 15:11:56

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
     )

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
     )

回复收藏 0 原文

~没有更多了~