CODEWITHSUNDEEP
sqloracle-database
Renaud is Not Bill Gates
Renaud is Not Bill Gates

Reputation: 2084

Referencing an aliased subquery in Oracle

I have the following query : 

select
        A.A_ID,
        B.Lib,
        A.Lib,
        C.Lib,
        (SELECT count(*) FROM X WHERE A.A_ID = X.A_ID) AS countX,
        (SELECT count(*) FROM Y WHERE A.A_ID = Y.A_ID) AS countY,
        (SELECT count(*) FROM Z WHERE A.A_ID = Z.A_ID) AS countZ
    from
        A
    left outer join
        C
            on A.C_ID=C.C_ID
    left outer join
        B
            on A.B_ID=B.B_ID
    WHERE
        countX = 2 AND countY = 3
    ORDER BY 
        countZ DESC;

But this gives me the following error:

SQL Error [904] [42000]: ORA-00904: "COL_5_0_" : identificateur non valide

How can I solve this ?

Upvotes: 0

Views: 45

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

try like below, i think you missed type group by it would be order by

with cte as
(

select
        A.A_ID,
        B.Lib,
        A.Lib,
        C.Lib,
        (SELECT count(*) FROM X WHERE A.A_ID = X.A_ID) AS countX,
        (SELECT count(*) FROM Y WHERE A.A_ID = Y.A_ID) AS countY,
        (SELECT count(*) FROM Z WHERE A.A_ID = Z.A_ID) AS countZ
    from
        A
    left outer join
        C
            on A.C_ID=C.C_ID
    left outer join
        B
            on A.B_ID=B.B_ID
) select * from cte where countX = 2 AND countY = 3
    Order BY 
    countZ DESC

Upvotes: 1

Related Questions