使用ORDER BY和ROWNUM的双嵌套查询中的标识符无效 [英] Invalid identifier in double-nested query with ORDER BY and ROWNUM

问题描述

我正在使用Oracle，因此我需要在一个请求中同时使用ORDER BY和ROWNUM.我需要对内部查询加倍嵌套，因为我要先ORDER BY 应用，然后再ROWNUM=1 选择.

I'm on Oracle and I need to use both, ORDER BY and ROWNUM, in one request. I need to double-nest my inner query, because I want to apply ORDER BY first and select with ROWNUM=1 afterwards.

我的数据被最高级的O.ID过滤.但是，我的内部查询出现错误，因为O.ID是那里的未知标识符.

My data is filtered by O.ID on outmost level. However, I get an error in my inner query, because O.ID is an unknown identifier there.

我想要什么:

SELECT
  O.INSERTDATE OrderCreateDate,

  -- Determine delivery date
  (SELECT INSERTDATE FROM (
     SELECT OP2.FK_ORDER, DD.ID, DD.INSERTDATE FROM MY_DELIVERYDATE_TABLE DD
       JOIN MY_ORDERPOS_TABLE OP2 ON DD.FK_ORDERPOS=OP2.ID
       LEFT OUTER JOIN MY_ORDER_TABLE O2 ON OP2.FK_ORDER=O2.ID
       WHERE OP2.FK_ORDER=O.ID AND -- This gives me "Invalid identifier O.ID"
             DD.DELFLAG IS NULL AND OP2.DELFLAG IS NULL
       ORDER BY DD.CLOSED ASC, ABS(TRUNC(CURRENT_DATE-TO_DATE(TO_CHAR(DD.INSERTDATE, 'DDMMYYYY'), 'DDMMYYYY'))) ASC
  ) WHERE ROWNUM=1) DeliveryDate

FROM MY_ORDER_TABLE O
WHERE O.ID = 620; -- ID goes here!

我能做到这一点的唯一方法是，当我在中间SELECT查询的WHERE子句中进行过滤时.但这当然很慢，因为内部SQL无需过滤即可返回整个数据.

The only way I get this working, is when I filter in the WHERE clause of the intermediate SELECT query. But this is slow, of course, since the inner SQL returns the entire data without filtering.

SELECT
  O.INSERTDATE OrderCreateDate,

  -- Determine delivery date
  (SELECT INSERTDATE FROM (
     SELECT OP2.FK_ORDER, DD.ID, DD.INSERTDATE FROM MY_DELIVERYDATE_TABLE DD
       JOIN MY_ORDERPOS_TABLE OP2 ON DD.FK_ORDERPOS=OP2.ID
       LEFT OUTER JOIN MY_ORDER_TABLE O2 ON OP2.FK_ORDER=O2.ID
       WHERE DD.DELFLAG IS NULL AND OP2.DELFLAG IS NULL
       ORDER BY DD.CLOSED ASC, ABS(TRUNC(CURRENT_DATE-TO_DATE(TO_CHAR(DD.INSERTDATE, 'DDMMYYYY'), 'DDMMYYYY'))) ASC
  ) WHERE ROWNUM=1 AND FK_ORDER=O.ID) DeliveryDate -- Filtering here

FROM MY_ORDER_TABLE O
WHERE O.ID = 620;

如何将O.ID传递给内部查询，或者如何重新设计此查询，同时仍然保持ORDER BY和ROWNUM的工作.

How can I pass O.ID to the inner query or how can this query be redesigned, still keeping ORDER BY and ROWNUM work.

我的最终解决方案，由Kim Berg Hansen建议，并通过轮辋进行了改进:

My final solution as suggested by Kim Berg Hansen and improved by rims:

(不过，我必须使用MIN()而不是MAX())

(I had to use MIN() instead of MAX(), though)

SELECT
  O.INSERTDATE OrderCreateDate,

  -- Determine delivery date
  (SELECT MIN(DD.INSERTDATE) KEEP (DENSE_RANK FIRST ORDER BY
    DD.CLOSED ASC, ABS(TRUNC(CURRENT_DATE-TRUNC(DD.INSERTDATE))) ASC)
   FROM MY_DELIVERYDATE_TABLE DD
   JOIN MY_ORDERPOS_TABLE OP2 ON DD.FK_ORDERPOS=OP2.ID
   LEFT OUTER JOIN MY_ORDER_TABLE O2 ON OP2.FK_ORDER=O2.ID
   WHERE OP2.FK_ORDER=O.ID AND
         DD.DELFLAG IS NULL AND OP2.DELFLAG IS NULL
  ) DeliveryDate

FROM MY_ORDER_TABLE O
WHERE O.ID = 620; -- ID goes here!

推荐答案

在使用的标量子查询中，您只能引用主"查询中的表向下嵌套一层"，而不能再向下引用，因为你见过. (我相信此限制在版本12中已解除，因此也许您可以只升级数据库?;-)

In the scalar subquery you are using, you can only reference the tables from the "main" query "one nested level down", not any further down, as you have seen. (I believe this restriction is lifted in version 12, so maybe you can just upgrade your database? ;-)

在标量子查询中，您尝试根据您的顺序获取第一行的INSERTDATE列的值.也可以不嵌套而编写，如下所示:

In the scalar subquery you are trying to get the value of INSERTDATE column of the first row according to your ordering. That can also be written without nesting as follows:

SELECT
O.INSERTDATE OrderCreateDate,

-- Determine delivery date
(SELECT MAX(DD.INSERTDATE) KEEP (
          DENSE_RANK FIRST ORDER BY
          DD.CLOSED ASC, ABS(TRUNC(CURRENT_DATE-TO_DATE(TO_CHAR(DD.INSERTDATE, 'DDMMYYYY'), 'DDMMYYYY'))) ASC
        )
   FROM MY_DELIVERYDATE_TABLE DD
   JOIN MY_ORDERPOS_TABLE OP2 ON DD.FK_ORDERPOS=OP2.ID
   LEFT OUTER JOIN MY_ORDER_TABLE O2 ON OP2.FK_ORDER=O2.ID
   WHERE OP2.FK_ORDER=O.ID AND -- This will no longer give "Invalid identifier O.ID"
         DD.DELFLAG IS NULL AND OP2.DELFLAG IS NULL
) DeliveryDate

FROM MY_ORDER_TABLE O
WHERE O.ID = 620; -- ID goes here!

KEEP(DENSE_RANK FIRST告诉MAX函数，它应该计算在ORDER BY子句中排名第一的那些行的MAX only .因此，如果您的ORDER BY是唯一的"，则MAX仅应用于一个行.如果您的ORDER BY不是唯一的"并且可以重复，则您可能会考虑要使用MAX还是MIN(或添加令ORDER BY变得独一无二).

KEEP (DENSE_RANK FIRST tells the MAX function, that it should calculate the MAX only of those rows that rank first in the ORDER BY clause. So if your ORDER BY is "unique", MAX will be applied only to one row. If your ORDER BY is not "unique" and can have duplicates, you might think about whether you want the MAX or the MIN (or add something to the ORDER BY to make it unique.)

(如果您使用的是Oracle 12，则可以替代KEEP(DENSE_RANK技巧是使用SELECT语句的FIRST 1 ROW ONLY子句.)

(If you had been on Oracle version 12, an alternative to the KEEP (DENSE_RANK trick would be to use the FIRST 1 ROW ONLY clause of the SELECT statement.)

这篇关于使用ORDER BY和ROWNUM的双嵌套查询中的标识符无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！