cutie_smily
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Thanks. My question was how can we achieve this without summing the columns twice.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER