Invalid identifier error on field created with select statement
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
精彩评论