CODEWITHSUNDEEP
oracle-databaseunionalias
Arun Vijay
Arun Vijay

Reputation: 121

Column Alias cannot access in outer query in Oracle

select k.Val, sum(k.Cnt) "Cnt" from 
(
(select a.Env_Location "Val", count( a.VolumeID ) "Cnt"
    from DTree
    join ZCOP_APLNG_Documents a on
     DTree.DataID = a.DataID and DTree.VersionNum = a.VersionNum
    where
     DTree.OwnerID =  -2111 and
     DTree.SubType not in ( 0, 136 ) and
     a.Env_Location is not NULL
     group by a.Env_Location
     )

union
     (select
     b.Env_Location "Val",  count( b.VolumeID ) "Cnt"
    from DTree
    join ZCOP_APLNG_Corr b on
     DTree.DataID = b.DataID and DTree.VersionNum = b.VersionNum
    where
     DTree.OwnerID = -2111 and
     DTree.SubType not in ( 0, 136 ) and
     b.Env_Location is not NULL
     group by b.Env_Location
     )
 ) k     
    group by k.Val

can anybody help me to make this work. Showing error Val or Cnt is invalid identifier. Can't we use some column alias for columns??

Upvotes: 1

Views: 1431

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

If you want to use case-sensitive identifiers (almost always a bad idea), every reference to that identifier would need to be case sensitive. In your case, "Val" and "Cnt" are both case-sensitive identifiers so you would need to refer to them using the case-sensitive syntax every time. Something like

SELECT k."Val", sum(k."Cnt") "Cnt" from 
   ...
GROUP BY k."Val"

In the vast majority of cases, you really don't want to use case sensitive aliases. You would generally be much better served with

SELECT k.val, sum(k.cnt) cnt from 
(
  SELECT a.env_location val, count( a.volumeID ) cnt
  ...
  UNION 
  SELECT b.env_location val, count( b.volumeID) cnt
  ...
) k
 GROUP BY k.val

Upvotes: 1

Related Questions