这个oracle查询有什么问题?
问题描述:
SELECT *
FROM (SELECT ROWNUM rnum,
query.*
FROM (WITH myQuery AS(
SELECT column_b
FROM table_a a
WHERE a.column_a = 1234)
SELECT b.column_e AS some_column
FROM table_b b,
table_c c,
table_a a
LEFT JOIN table_d d ON c.column_c = d.column_d
JOIN myQuery mq ON a.column_b = mq.column_b
WHERE b.column_b = a.column_b) query)
WHERE rnum > 0
答
不要混合使用ANSI-88和ANSI-92 JOIN语法,选择一个或其他。下面是使用ANSI-92语法查询:
WITH myQuery AS (
SELECT column_b
FROM table_a a
WHERE a.column_a = 1234)
SELECT x.*
FROM (SELECT b.column_e AS some_column,
ROWNUM 'rnum'
FROM table_b b
JOIN TABLE_A a ON a.column_b = b.column_b
JOIN myQuery mq ON mq.column_b = a.column_b
JOIN table_c c ON c.? = ?? --need join criteria here
LEFT JOIN table_d d ON c.column_c = d.column_d) x
WHERE x.rnum > 0
你举的例子没有什么TABLE_C加入到 - 因此?
和??
我不知道WITH
子句可以在子查询中被定义 - 我当然,我以前在10g尝试过时遇到了一个错误。
+0
-1您的观点#1 - 可以在子查询中定义WITH子句。 +1重新指出#2 - 新旧连接语法的混合是ORA-00904的原因。 – 2009-12-22 05:24:25
您是否收到错误消息或意外结果? – harto 2009-12-22 01:13:35
错误消息,在第二个连接条件,说ORA-00904的,无效的字符串标识符 – user61734 2009-12-22 01:15:36
没了,我查了列名,他们是正确的Oracle的 – user61734 2009-12-22 01:21:18