| Same query not working in different schema's [message #606414] |
Thu, 23 January 2014 09:42  |
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
|
Senior Member |
|
|
Dear Friend,
I have a peculiar issue, where my below query is able to fetch me results in one schema, where as not in the other schema. The error i face is ORA-00904: "B"."ASC_ITEM_CODE": invalid identifier. Actually it is a bigger query, i broke down to make it small to get the same error as i got in original query.
Error found in : Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
Working fine in : Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
Both have same : PL/SQL Release 10.2.0.1.0 - Production
Is that 64bi and Prod, makes a difference.
SELECT asc_item_code,
asc_item_name,
asc_grade_code_1,
asc_alloc_qty,
(SELECT lcs_stk_qty_bu
FROM (SELECT lcs_stk_qty_bu
FROM os_locn_curr_stk
WHERE B.asc_item_code = lcs_item_code))ASC_FREE_STK
FROM ov_alloc_stk_ctc B;
Pls help.
Regards
RajS
|
|
|
|
| Re: Same query not working in different schema's [message #606415 is a reply to message #606414] |
Thu, 23 January 2014 09:53  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That query shouldn't work in either enviroment.
When referring to a table defined in an outer query you're only allowed to refer to tables that are defined one level out.
In your example b is defined two levels out.
That only works because of a bug - you'll have to rewrite it.
|
|
|
|
|
|
| Re: Same query not working in different schema's [message #606430 is a reply to message #606417] |
Thu, 23 January 2014 13:47 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 23 January 2014 11:14
I confirm, it is a bug in 10.2.0.1 and only 10.2.0.1.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for HPUX: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> select (select dummy z from (select dummy y from dual where dummy=d.dummy)) z from dual d
2 /
select (select dummy z from (select dummy y from dual where dummy=d.dummy)) z from dual d
*
ERROR at line 1:
ORA-00904: "D"."DUMMY": invalid identifier
SQL> connect scott@pdborcl12
Enter password: *****
Connected.
SCOTT@pdborcl12 > select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production
SCOTT@pdborcl12 > select (select dummy z from (select dummy y from dual where dummy=d.dummy)) z from dual d
2 /
Z
-
X
SCOTT@pdborcl12 >
And:
SCOTT@pdborcl12 > var c clob
SCOTT@pdborcl12 > exec dbms_utility.expand_sql_text('select (select dummy z from (select dummy y from dual where dummy=d.dummy)) z from dual d',:c);
PL/SQL procedure successfully completed.
SCOTT@pdborcl12 > print c
C
---------------------------------------------------------------
SELECT (SELECT "A1"."DUMMY" "Z" FROM (SELECT "A3"."DUMMY" "Y"
FROM "SYS"."DUAL" "A3" WHERE "A3"."DUMMY"=:B1) "A2") "Z" FROM
"SYS"."DUAL" "A1"
SCOTT@pdborcl12 >
Didn't have a chance to open SR with Oracle to find out if it is a bug or starting 12C we can correlate more than one level deep (nothing in docs - at least I couldn't find any references).
SY.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
