ORA-00936：缺少表达式oracle [英] ORA-00936: missing expression oracle

问题描述

我有这个查询

SELECT DAL_ROWNOTABLE.DAL_ID FROM 
(
    SELECT ticket.id AS "DAL_ID",  ROWNUMBER ( Order By ticket.id  )  AS "DAL_ROWNUMBER" 
    FROM ticket_table ticket 
    WHERE ( ticket.type = N'I' ) 
    AND 
    ( 
        ticket.tenant IS NULL OR ticket.tenant IN 
        (
            SELECT  * FROM 
            ( 
                SELECT tenant_group_member.tenant_id 
                FROM tenant_group_member 
                WHERE tenant_group_member.tenant_group = HEXTORAW('30B0716FEB5F4E4BB82A7B7AA3A1A42C') 
                ORDER BY ticket.id 
            ) 
         ) 
     )
) DAL_ROWNOTABLE 
WHERE DAL_ROWNOTABLE.DAL_ROWNUMBER BETWEEN 1 AND 21

抛出ORA-00936缺少表达式的allow查询有什么问题？任何人？任何帮助将不胜感激...错误抛出在第一个开头的第80列：

What is the problem with the allow query that is throwing ORA-00936 missing expression? anyone? Any help will be appreciated...Error thrown at column:80 which is at the beginning of first order by:

推荐答案

查询可以大大简化。它有诸如子查询的额外层次和在在子查询中不必要的订单。你想用 rownumber 做什么你可以使用 rownum ：

Your query can be much simplified. It has things like extra layers of subqueries and an unnecessary order by in an in subquery. What you want to do with rownumber you can do with just rownum:

SELECT DAL_ROWNOTABLE.DAL_ID
FROM (SELECT ticket.id AS "DAL_ID" 
      FROM ticket_table ticket 
      WHERE (ticket.type = N'I' ) AND 
            (ticket.tenant IS NULL OR
             ticket.tenant IN (SELECT tgm.tenant_id 
                               FROM tenant_group_member tgm
                               WHERE tgm.tenant_group = HEXTORAW('30B0716FEB5F4E4BB82A7B7AA3A1A42C') 
                              ) 
            )
      ORDER BY ticket.id
     ) DAL_ROWNOTABLE 
WHERE rownum <= 21;

这篇关于ORA-00936：缺少表达式oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！