Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Issue with Alias and Union of CTEs

995588Jun 14 2013 — edited Jun 14 2013

I have a query that works in SQL Server but I'm having issues with it in PL/SQL.  I keep running into the error saying "invalid identifier" when I try to reference PlantNumber or Plant_No or any other piece of the CTE or subquery.  Is this not possible with Oracle?

Here's a shortened version of my query:

WITH RemoveData AS
   (
   SELECT a.PLANT_NO,a.ALLOC_WHDV_VOL,a.KW_CTR_REDELIVERED_HV, a.MTR_NO, a.MTR_SFX, a.TRNX_ID, a.REC_STATUS_CD,
MAX(a.ACCT_DT) ACCT_DT
   FROM GasStmt a
   WHERE a.REC_STATUS_CD = 'RR'
   GROUP BY a.PLANT_NO,a.ALLOC_WHDV_VOL,a.KW_CTR_REDELIVERED_HV, a.MTR_NO, a.MTR_SFX, a.TRNX_ID, a.REC_STATUS_CD
   HAVING COUNT(a.REC_STATUS_CD) > 2
   ),
  RemoveData2 AS
   (
   SELECT plant_no "PlantNumber"
   ,SUM(-a.ALLOC_WHDV_VOL) "PlantStandardGrossWellheadMcf"
   ,SUM(KW_CTR_REDELIVERED_HV) "KeepWholeResidueMMBtu"
   FROM RemoveData a
   GROUP BY plant_no
   ),
  OriginalData AS
   (
   SELECT a.PLANT_NO "PlantNumber"
   ,SUM(a.ALLOC_WHDV_VOL) "PlantStandardGrossWellheadMcf"
   ,SUM(CASE WHEN a.REC_STATUS_CD = 'RR' THEN -a.KW_CTR_REDELIVERED_HV ELSE a.KW_CTR_REDELIVERED_HV END) "KeepWholeResidueMMBtu"
   FROM GasStmt a
   LEFT OUTER JOIN (SELECT MTR_NO, MTR_SFX, TRNX_ID, REC_STATUS_CD, MAX(ACCT_DT) ACCT_DT
   FROM GasStmt
   WHERE REC_STATUS_CD = 'RR'
   GROUP BY MTR_NO, MTR_SFX, TRNX_ID, REC_STATUS_CD
   HAVING COUNT(TRNX_ID) > 1) b
   ON a.MTR_NO = b.MTR_NO
   AND a.TRNX_ID = b.TRNX_ID
   AND a.Rec_Status_Cd = b.REC_STATUS_CD
   AND a.Acct_Dt = b.ACCT_DT
   WHERE a.ACCT_DT > '1/1/2010'
   AND b.MTR_NO IS NULL
   GROUP BY a.PLANT_NO
   ),
UnionCTE AS (  
SELECT *
FROM RemoveData2
UNION
SELECT *
FROM OriginalData
)
SELECT PlantNumber, SUM(PlantStandardGrossWellheadMcf) AS PlantStandardGrossWellheadMcf,SUM(KeepWholeResidueMMBtu) AS KeepWholeResidueMMBtu
FROM UnionCTE
GROUP BY PlantNumber

It's the bottom select from UnionCTE that's causing the issue.  Any tips would be appreciated!

This post has been answered by Frank Kulash on Jun 14 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 12 2013
Added on Jun 14 2013
3 comments
1,331 views