时间戳

首页 > 解决方案 > 如何将主查询列值传递给嵌套子查询 Where 条件？

c#-4.0 - 如何将主查询列值传递给嵌套子查询 Where 条件？

问题描述

我正在使用嵌套子查询编写此查询，以查找PREPARED_BY, VERIFIED_BY，AUTHORIZED_BY具体取决于CONDATE表Expenditure，但在我的子查询中，无法识别Expenditure表对象CONDATE并引发此错误：

ORA-00904: "EX"."CONDATE": 标识符无效。

代码：

SELECT ex.conno,
       ex.itemno,
       ex.adv_no || ' ' || to_char(ex.condate, 'DD-MON-YYYY') chequenodate,
       ex.conname,
       ex.apaid,
       ex.dpayment,
       gf.gf_name,
       expenditure_type,
       ex.off_code,
       ofc.officename,
       ex.remarks,
       (SELECT prepared_by
        FROM   (SELECT prepared_by
                FROM   authorization
                WHERE  (pre_last_date >= ex.condate OR pre_last_date IS NULL)
                AND    project_id = 128
                ORDER  BY id ASC)
        WHERE  rownum = 1) AS prepared_by,
       (SELECT verified_by
        FROM   (SELECT verified_by
                FROM   authorization
                WHERE  (ve_last_date >= ex.condate OR ve_last_date IS NULL)
                AND    project_id = 128
                ORDER  BY id ASC)
        WHERE  rownum = 1) AS verified_by,
       (SELECT authorized_by
        FROM   (SELECT authorized_by
                FROM   authorization
                WHERE  (au_last_date >= ex.condate OR au_last_date IS NULL)
                AND    project_id = 128
                ORDER  BY id ASC)
        WHERE  rownum = 1) AS authorized_by

FROM   expenditure ex
INNER  JOIN officecode ofc
ON     ofc.off_code = ex.off_code
INNER  JOIN coa_category ca
ON     ca.coa_cat_id = ex.coa_cat_id
INNER  JOIN g_fund_type gf
ON     gf.gf_type_id = ca.gf_type_id
WHERE  ex.conno = 'MGSP/PMU/NON/145'
AND    ex.itemno = 149;

标签： c#-4.0oracle11goracle10g

解决方案

您遇到的问题是父表只能被向下一级的子查询引用。您正在尝试访问子查询中父表中的列向下两个级别，因此您会收到错误消息。

为了访问子查询中的父列，您需要重写它，使其仅向下一层。

这可以通过使用KEEP FIRST/LAST聚合函数来实现，例如：

SELECT ex.conno,
       ex.itemno,
       ex.adv_no || ' ' || to_char(ex.condate, 'DD-MON-YYYY') chequenodate,
       ex.conname,
       ex.apaid,
       ex.dpayment,
       gf.gf_name,
       expenditure_type,
       ex.off_code,
       ofc.officename,
       ex.remarks,
       (SELECT MAX(a.prepared_by) KEEP (dense_rank FIRST ORDER BY a.id ASC)
        FROM   authorizatiion a
        WHERE  (a.pre_last_date >= ex.condate OR a.pre_last_date IS NULL)
        AND     a.project_id = 128) prepared_by,
       (SELECT MAX(a.verified_by) KEEP (dense_rank FIRST ORDER BY a.id ASC)
        FROM   authorizatiion a
        WHERE  (a.ve_last_date >= ex.condate OR a.ve_last_date IS NULL)
        AND     a.project_id = 128) verified_by,
       (SELECT MAX(a.authorized_by) KEEP (dense_rank FIRST ORDER BY a.id ASC)
        FROM   authorizatiion a
        WHERE  (a.au_last_date >= ex.condate OR a.au_last_date IS NULL)
        AND     a.project_id = 128) authorized_by
FROM   expenditure ex
       INNER JOIN officecode ofc ON ofc.off_code = ex.off_code
       INNER JOIN coa_category ca ON ca.coa_cat_id = ex.coa_cat_id
       INNER JOIN g_fund_type gf ON gf.gf_type_id = ca.gf_type_id
WHERE  ex.conno = 'MGSP/PMU/NON/145'
AND    ex.itemno = 149;

注意我在这里使用MAX过FIRST；这意味着如果有多行具有相同的最低 id，则将使用prepared_by 列的最高值。MIN如果您想要最低值，您可以将其更改为。这仅在每个 id 有超过一行的情况下才有意义，否则它只会返回最低 id 的prepared_by 列的值。

推荐阅读