Oracle第二层子查询居然不认最外层表中的列,在某些版本中一直存在.
2010-04-20 15:41 Tracy. 阅读(2847) 评论(0) 编辑 收藏 举报至少我公司版本10.2.0.3还有这个问题,ANSI SQL的问题.转自Asktom,原帖链接.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1853075500346799932
You Asked
create table con ( content_id number); create table mat ( material_id number, content_id number, resolution number, file_location varchar2(50)); create table con_groups (content_group_id number, content_id number); insert into con values (99); insert into mat values (1, 99, 7, 'C:\foo.jpg'); insert into mat values (2, 99, 2, '\\server\xyz.mov'); insert into mat values (3, 99, 5, '\\server2\xyz.wav'); insert into con values (100); insert into mat values (4, 100, 5, 'C:\bar.png'); insert into mat values (5, 100, 3, '\\server\xyz.mov'); insert into mat values (6, 100, 7, '\\server2\xyz.wav'); insert into con_groups values (10, 99); insert into con_groups values (10, 100); commit; SELECT m.material_id, (SELECT file_location FROM (SELECT file_location FROM mat WHERE mat.content_id = m.content_id ORDER BY resolution DESC) special_mats_for_this_content WHERE rownum = 1) special_mat_file_location FROM mat m WHERE m.material_id IN (select material_id from mat inner join con on con.content_id = mat.content_id inner join con_groups on con_groups.content_id = con.content_id where con_groups.content_group_id = 10);
Please consider the number 10 at the end of the query to be a parameter. In other words this value is just hardcoded in this example; it would change depending on the input.
My question is: Why do I get the error
"M"."CONTENT_ID": invalid identifierfor the nested, correlated subquery? Is there some sort of nesting limit? This subquery needs to be ran for every row in the resultset because the results will change based on the content_id, which can be different for each row. How can I accomplish this with Oracle?
Not that I'm trying to start a SQL Server vs Oracle discussion, but I come from a SQL Server background and I'd like to point out that the following, equivalent query runs fine on SQL Server:
create table con ( content_id int); create table mat ( material_id int, content_id int, resolution int, file_location varchar(50)); create table con_groups (content_group_id int, content_id int); insert into con values (99); insert into mat values (1, 99, 7, 'C:\foo.jpg'); insert into mat values (2, 99, 2, '\\server\xyz.mov'); insert into mat values (3, 99, 5, '\\server2\xyz.wav'); insert into con values (100); insert into mat values (4, 100, 5, 'C:\bar.png'); insert into mat values (5, 100, 3, '\\server\xyz.mov'); insert into mat values (6, 100, 7, '\\server2\xyz.wav'); insert into con_groups values (10, 99); insert into con_groups values (10, 100); SELECT m.material_id, (SELECT file_location FROM (SELECT TOP 1 file_location FROM mat WHERE mat.content_id = m.content_id ORDER BY resolution DESC) special_mats_for_this_content ) special_mat_file_location FROM mat m WHERE m.material_id IN (select material_id from mat inner join con on con.content_id = mat.content_id inner join con_groups on con_groups.content_id = con.content_id where con_groups.content_group_id = 10);
Can you please help me understand why I can do this in SQL Server but not Oracle?
Thanks,
Nate
and we said...
ANSI SQL has table references (correlation names) scoped to just one level deepops$tkyte%ORA10GR2> select (select count(*) from (select * from scott.emp where ename = dual.dummy)) from dual; select (select count(*) from (select * from scott.emp where ename = dual.dummy)) from dual * ERROR at line 1: ORA-00904: "DUAL"."DUMMY": invalid identifier ops$tkyte%ORA10GR2> select (select count(*) from scott.emp where ename = dual.dummy) from dual; (SELECTCOUNT(*)FROMSCOTT.EMPWHEREENAME=DUAL.DUMMY) -------------------------------------------------- 0
the first one fails because we tried to push the dual.dummy reference two levels down - in correlated subqueries, they only go a level.
ops$tkyte%ORA10GR2> SELECT m.material_id, m.content_id, 2 (SELECT max(file_location) keep (dense_rank first order by resolution desc) 3 FROM mat 4 WHERE mat.content_id = m.content_id) special_mat_file_location 5 FROM mat m 6 WHERE m.material_id IN (select material_id 7 from mat 8 inner join con on con.content_id = mat.content_id 9 inner join con_groups on con_groups.content_id = con.content_id 10 where con_groups.content_group_id = 10); MATERIAL_ID CONTENT_ID SPECIAL_MAT_FILE_LOCATION ----------- ---------- -------------------------------------------------- 1 99 C:\foo.jpg 2 99 C:\foo.jpg 3 99 C:\foo.jpg 4 100 \\server2\xyz.wav 5 100 \\server2\xyz.wav 6 100 \\server2\xyz.wav 6 rows selected.
本文来自博客园,作者:Tracy.,转载请注明原文链接:https://www.cnblogs.com/tracy/archive/2010/04/20/1716260.html