Hi All,
Given the following SQL
SELECT E1.WORKORDER AS WORKORDER2,
E1.DATEREPORTED AS DATEREPORTED,
E1.CLIENTPROJECTNO AS CLIENTPROJECTNO,
E1.CONSULTANT_NAME AS CONSULTANT_NAME,
E1.CONTACT_NAME AS CONTACT_NAME,
E1.DATESUBMITTED AS DATESUBMITTED,
(SELECT COUNT(1) AS A1
FROM (SELECT (
SELECT COUNT(1) AS A1
FROM TBLPACKAGE Extent4
WHERE (Project2.SAMPLEINDEX = Extent4.SAMPLEINDEX)
) AS C1
FROM
(SELECT Extent3.SAMPLEINDEX AS SAMPLEINDEX FROM TBLSAMPLE Extent3
WHERE E1.WORKORDER=Extent3.WORKORDER ) Project2
) Project3
WHERE (Project3.C1 > 0)
) AS C1
FROM TBLWORKORDER E1;
----
The above three tables are related as an individual WORKORDER has 1 or more
SAMPLES
each of which has 1 or more PACKAGES attached to it.
The above executes in 12.1, our staging environment.
It fails in 11.2.0.3, our production environment complaining
WHERE E1.WORKORDER=Extent3.WORKORDER
"invalid identifier E1"
I assume because the inner query cannot see the outer alias.
Did scoping rules change between the 2 versions?
And yes our "sophisticated developers" have been told to rewrite it as a normal
or ANSI join :)
I suppose I could do it for them, but there's rather a lot of these queries,
and well, they
wouldn't learn anything if I did it :)
TIA
Dave
--
Dave Morgan
Senior Consultant, 1001111 Alberta Limited
dave.morgan@xxxxxxxxxxx
403 399 2442
--
//www.freelists.org/webpage/oracle-l
