开发者

Invalid identifier error on field created with select statement

开发者 https://www.devze.com 2023-04-12 20:59 出处:网络
Why sql bellow don\'t work? select a.field1, a.field2, a.field3, (select count(*) from table2 b where b.field1 = a.field1

Why sql bellow don't work?

select
    a.field1, a.field2, a.field3,
    (select count(*)
        from table2 b
        where b.field1 = a.field1
    ) as field4,
    (select count(*)
        from ta开发者_开发技巧ble3 b
        where b.field1 = a.field1
    ) as field5,
    (select count(*)
        from table4 b
        where b.field1 = a.field1
    ) as field6,
from table1 a
order by field4

Oracle says: ORA-00904: "field4": invalid identifier


try to wrap it up

select * from 
(    
select
    a.field1, a.field2, a.field3,
    (select count(*)
        from table2 b
        where b.field1 = a.field1
    ) as field4,
    (select count(*)
        from table3 b
        where b.field1 = a.field1
    ) as field5,
    (select count(*)
        from table4 b
        where b.field1 = a.field1
    ) as field6,
from table1 a
)
order by field4
0

精彩评论

暂无评论...
验证码 换一张
取 消