• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 22213
  • Last Modified:

Oracle Column Alias

Hi All,
 Why cant I use the column alias on the having clause or in the where clause. Am I missing anything or Oracle doesnt support aliases other than order by clause?

If I want to re-write the below query how can I do without re-summing the columns again in the having clause.

Thanks,

*****************************************************************************************
SELECT
    FILL_DATE,
    TRIM(D.GROUP_NBR) || TRIM(MEM.SUBSCRIBER_NBR) GRP_SUBS,
    PHR_NBR#,
    RX_NUM,
    REFILL,
    DRUG#,
    SUM(MTUNITS)                   SUM_MTUNITS,
    SUM(COPAY)                     SUM_COPAY,
    SUM(TOTAL_AMOUNT_DUE)          SUM_AMTDUE,
    SUM(DAYS_SUPPLY)               SUM_DAYSUPP,
    SUM(PROF_FEE)                  SUM_PROFFEE,
    SUM(ADD_COPAY)                 SUM_ADDCOPAY
   
FROM STG.DRUG D
LEFT OUTER JOIN STG.Member  MEM
ON TRIM(D.MEMBER) = MEM.CES_ALT_ID
WHERE
D.FILL_DATE BETWEEN MEM.EFF_DT AND MEM.END_DT AND
MEM.VOID_FLAG <> 'V'
GROUP BY
    FILL_DATE,
    TRIM(D.GROUP_NBR) || TRIM(MEM.SUBSCRIBER_NBR),
    PHR_NBR#,
    RX_NUM,
    REFILL,
    DRUG#
HAVING  SUM_COPAY <> 0 OR SUM_AMTDUE <> 0 OR SUM_MTUNITS <> 0
0
cutie_smily
Asked:
cutie_smily
  • 3
  • 2
3 Solutions
 
johnsoneSenior Oracle DBACommented:
No, you cannot use an alias in a where or group by.

The technicality of it is that when the where clause and the group by clause are being executed, the select part of the query has not run and the alias has not been assigned.  Since the order by is technically done after the select the aliases can be used.
0
 
cutie_smilyAuthor Commented:
Thanks for making it clear. Please help in re-writing the query to achieve the same results.
0
 
johnsoneSenior Oracle DBACommented:
It looks like they were only in the HAVING.

SELECT
    FILL_DATE,
    TRIM(D.GROUP_NBR) || TRIM(MEM.SUBSCRIBER_NBR) GRP_SUBS,
    PHR_NBR#,
    RX_NUM,
    REFILL,
    DRUG#,
    SUM(MTUNITS)                   SUM_MTUNITS,
    SUM(COPAY)                     SUM_COPAY,
    SUM(TOTAL_AMOUNT_DUE)          SUM_AMTDUE,
    SUM(DAYS_SUPPLY)               SUM_DAYSUPP,
    SUM(PROF_FEE)                  SUM_PROFFEE,
    SUM(ADD_COPAY)                 SUM_ADDCOPAY
   
FROM STG.DRUG D
LEFT OUTER JOIN STG.Member  MEM
ON TRIM(D.MEMBER) = MEM.CES_ALT_ID
WHERE
D.FILL_DATE BETWEEN MEM.EFF_DT AND MEM.END_DT AND
MEM.VOID_FLAG <> 'V'
GROUP BY
    FILL_DATE,
    TRIM(D.GROUP_NBR) || TRIM(MEM.SUBSCRIBER_NBR),
    PHR_NBR#,
    RX_NUM,
    REFILL,
    DRUG#
HAVING  SUM(COPAY) <> 0 OR SUM(TOTAL_AMOUNT_DUE) <> 0 OR SUM(MTUNITS) <> 0
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
cutie_smilyAuthor Commented:
Thanks. My question was how can we achieve this without summing the columns twice.
0
 
johnsoneSenior Oracle DBACommented:
You are not summing the columns twice.  It only sums the columns once.  The having is a check after the fact.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
You can do it as shown below :

select * -- pick only whatever columns you need from inner columns which are existing
from
(
SELECT
    FILL_DATE,
    TRIM(D.GROUP_NBR) || TRIM(MEM.SUBSCRIBER_NBR) GRP_SUBS,
    PHR_NBR#,
    RX_NUM,
    REFILL,
    DRUG#,
    SUM(MTUNITS)                   SUM_MTUNITS,
    SUM(COPAY)                     SUM_COPAY,
    SUM(TOTAL_AMOUNT_DUE)          SUM_AMTDUE,
    SUM(DAYS_SUPPLY)               SUM_DAYSUPP,
    SUM(PROF_FEE)                  SUM_PROFFEE,
    SUM(ADD_COPAY)                 SUM_ADDCOPAY    
FROM STG.DRUG D
LEFT OUTER JOIN STG.Member  MEM
ON TRIM(D.MEMBER) = MEM.CES_ALT_ID
WHERE
D.FILL_DATE BETWEEN MEM.EFF_DT AND MEM.END_DT AND
MEM.VOID_FLAG <> 'V'
GROUP BY
    FILL_DATE,
    TRIM(D.GROUP_NBR) || TRIM(MEM.SUBSCRIBER_NBR),
    PHR_NBR#,
    RX_NUM,
    REFILL,
    DRUG# )
where SUM_COPAY <> 0 OR SUM_AMTDUE <> 0 OR SUM_MTUNITS <> 0;

You can refer to any of the columns inside the inline view which can be
in the select list, where clause, group by , having, order etc.

This is just to show you that we can use inline views to consider the
data returned as a table itself but not real table.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now