MySQL 开发人员在这里 -- 在 Oracle 10g 中嵌套 select * 挑剔吗？

发布于 2024-09-01 17:40:55 字数 701 浏览 3 评论 0原文

我正在编写一个简单的诊断查询，然后尝试在 Oracle 10g SQL Scratchpad 中执行它。编辑：它不会在代码中使用。我正在嵌套一个简单的“Select *”，但它给了我错误。

在 SQL Scratchpad for Oracle 10g Enterprise Manager Console 中，此语句运行良好。

SELECT *  FROM v$session sess, v$sql     sql  WHERE sql.sql_id(+) = sess.sql_id and sql.sql_text <> ' '

如果我尝试将其包装在 Select * from () tb2 中，则会收到错误“ORA-00918：列定义不明确”。我没想到这种声明会发生这种情况，所以我有点困惑。

 select * from
 (SELECT *  FROM v$session sess, v$sql     sql  WHERE sql.sql_id(+) = sess.sql_id and sql.sql_text <> ' ')
 tb2

据我所知，您应该始终能够使用此结构从另一个 select * 语句的结果集中选择 * ...对吧？

Oracle/10g/暂存器是否试图强迫我接受某种语法结构以防止过度嵌套？这是暂存器中的错误还是有关 Oracle 工作原理的错误？

原文

I'm writing a simple diagnostic query then attempting to execute it in the Oracle 10g SQL Scratchpad. EDIT: It will not be used in code. I'm nesting a simple "Select *" and it's giving me errors.

In the SQL Scratchpad for Oracle 10g Enterprise Manager Console, this statement runs fine.

SELECT *  FROM v$session sess, v$sql     sql  WHERE sql.sql_id(+) = sess.sql_id and sql.sql_text <> ' '

If I try to wrap that up in Select * from () tb2 I get an error, "ORA-00918: Column Ambiguously Defined". I didn't think that could ever happen with this kind of statement so I am a bit confused.

 select * from
 (SELECT *  FROM v$session sess, v$sql     sql  WHERE sql.sql_id(+) = sess.sql_id and sql.sql_text <> ' ')
 tb2

You should always be able to select * from the result set of another select * statement using this structure as far as I'm aware... right?

Is Oracle/10g/the scratchpad trying to force me to accept a certain syntactic structure to prevent excessive nesting? Is this a bug in scratchpad or something about how oracle works?

分享到QQ

分享到微博

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

发布评论

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

痴情换悲伤 2024-09-08 17:40:55

当 Oracle 解析 SELECT * 时，它将其扩展为要选择的列的实际列表。由于您的内联视图包含两个名为 SQL_ID 的列，因此这会导致引用不明确。

有趣的是，使用 ANSI 连接语法似乎会导致它自动为重复的列名添加别名，从而避免了错误。

select * from
(select * from v$session sess left outer join v$sql sql on sql.sql_id=sess.sql_id and sql.sql_text <> ' ')

顺便说一句，我不清楚您为什么在 sql_text 上选择该条件。我不希望该列包含单个空格。你真的想过滤掉 NULL 吗？如果是这样，为什么要使用外连接呢？

When Oracle parses a SELECT *, it expands it out to an actual list of the columns to be selected. Since your inline view contains two columns named SQL_ID, this results in an ambiguous reference.

Interestingly, using ANSI join syntax seems to cause it to alias the duplicate column names automatically, and therefore avoids the error.

select * from
(select * from v$session sess left outer join v$sql sql on sql.sql_id=sess.sql_id and sql.sql_text <> ' ')

Incidentally, it's not clear to me why you chose that condition on sql_text. I don't expect that column would ever contain a single space. Are you really trying to filter out NULLs? If so, why use an outer join at all?

回复收藏 0 原文