将外部选择行变量传递给 oracle 中的内部选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10922466/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pass an outer selects row variable to inner select in oracle
提问by surajz
How do you pass an outer selects row variable to inner select in oracle, here is a sample query ( other outer joins has been removed. This query will be loaded 1 time in life time of an application). This query works
如何将外部选择行变量传递给 oracle 中的内部选择，这是一个示例查询（其他外部连接已被删除。此查询将在应用程序的生命周期中加载 1 次）。此查询有效
select l5.HIERARCHY_ID,
(select wm_concat(isbn) isbns from (
select op.isbn from oproduct op
LEFT JOIN assignment ha on op.r.reference = ha.reference
where ha.hierarchy_id = '100589'))isbns
from level l5 where l5.gid = '1007500000078694'
but when I change the inner select's where clause
但是当我改变内部选择的 where 子句时
where ha.hierarchy_id = '100589'))isbns
to
where ha.hierarchy_id = l5.HIERARCHY_ID))isbns
I get the following error ORA-00904: "L5"."HIERARCHY_ID": invalid identifier
我收到以下错误 ORA-00904: "L5"."HIERARCHY_ID": invalid identifier
采纳答案by surajz
I did something like this to fix the problem. There was one unnecessary select
我做了这样的事情来解决这个问题。有一个不必要的选择
select
l5.HIERARCHY_ID,
(
select
wm_concat(op.isbn)
from
oproduct op
LEFT JOIN assignment ha on op.r.reference = ha.reference
where ha.hierarchy_id = l5.HIERARCHY_ID
) ISBNS
from
level l5
where
l5.gid = '1007500000078694'
回答by JHS
You cannot pass the value of a 2nd level SELECT.
您不能传递 2nd level 的值SELECT。
For example -
例如 -
SELECT value1 -- 1st level select
FROM (
SELECT value2 -- 2nd level select
FROM (
SELECT value3 -- 3rd level select.
You can have values from the 1st level SELECTavailable for only the second level SELECT.
Similarly the values in the second level SELECTare only available to the 1st level SELECTand the 3rd level SELECTnot beyond that.
您可以将第一级的值SELECT仅用于第二级SELECT。类似地，第二级SELECT中的值仅适用于第一级SELECT，而SELECT不能超过第三级。
回答by John D
I think I am reading your SQL correctly - you want an outer join when the hierarchy ids match?
我想我正在正确阅读您的 SQL - 当层次结构 id 匹配时，您想要一个外连接？
SELECT
l5.hierarchy_id,
op.isbns
FROM
LEVEL l5
LEFT OUTER JOIN
(SELECT
wm_concat (op.isbn) isbns,
ha.hierarch_id
FROM
oproduct op
LEFT JOIN
assignment ha
ON op.reference = ha.reference) op
ON l5.gid = op.hierarchy_id
