Home » SQL & PL/SQL » SQL & PL/SQL » Help with Alias
Help with Alias [message #188228] |
Thu, 17 August 2006 08:32  |
aadebayo
Messages: 38 Registered: August 2005
|
Member |
|
|
Hello
Please can somebody help me here. I have this query where I have assigned a value into an alias. I then need to refer to the alias within the where clause. Please can some one tell me how to do it. The alias that I need to reference in the where clause is called summary
The query is below
SELECT
nvl(O_SERVICE_PACKAGES.SPA_PER_GRO_ID,'NULL') Per_ID,
nvl(olm_bo.get_address(O_SERVICE_PACKAGES.SPA_PER_GRO_IND, O_SERVICE_PACKAGES.SPA_PER_GRO_ID),'NULL') Addr,
nvl(O_SERVICE_REGISTERS.SRE_OUN_ID,'NULL') Provider,
O_AGREEMENT_DETAILS.ADE_WEEKLY_VISITS WK_v,
( ( sum(nvl(O_ADE_TIMES.ATI_MON_HRS,0) + nvl(O_ADE_TIMES.ATI_MON_MINS/60,0)) )+( sum(nvl(O_ADE_TIMES.ATI_TUE_HRS,0) + nvl(O_ADE_TIMES.ATI_TUE_MINS/60,0)) )+( sum(nvl(O_ADE_TIMES.ATI_WED_HRS,0) + nvl(O_ADE_TIMES.ATI_WED_MINS/60,0)) )+( sum(nvl(O_ADE_TIMES.ATI_THU_HRS,0) + nvl(O_ADE_TIMES.ATI_THU_MINS/60,0)) )+( sum(nvl(O_ADE_TIMES.ATI_FRI_HRS,0) + nvl(O_ADE_TIMES.ATI_FRI_MINS/60,0)) )+( sum(nvl(O_ADE_TIMES.ATI_SAT_HRS,0) + nvl(O_ADE_TIMES.ATI_SAT_MINS/60,0)) )+( sum(nvl(O_ADE_TIMES.ATI_SUN_HRS,0) + nvl(O_ADE_TIMES.ATI_SUN_MINS/60,0)) ) )+( sum(nvl(O_ADE_TIMES.ATI_ANY_HRS,0)+nvl(O_ADE_TIMES.ATI_ANY_MINS/60,0)) )as summary
, nvl(O_AGREEMENT_DETAILS.ADE_TIMEBAND,'NULL') TimeBand,
nvl(initcap(sel_service_types.STY_NAME),'NULL')
FROM
O_SERVICE_PACKAGES,
O_SERVICE_REGISTERS,
O_SERVICE_TYPES sel_service_types,
O_SERVICE_TYPES,
O_ADE_TIMES,
O_SERVICE_ELEMENTS,
O_AGREEMENT_DETAILS
WHERE
( O_AGREEMENT_DETAILS.ADE_SPA_ID(+)=O_SERVICE_PACKAGES.SPA_ID )
AND ( O_ADE_TIMES.ATI_ADE_ID(+)=O_AGREEMENT_DETAILS.ADE_ID )
AND ( O_SERVICE_REGISTERS.SRE_ID(+)=O_AGREEMENT_DETAILS.ADE_SRE_ID )
AND ( O_SERVICE_TYPES.STY_ID(+)=O_AGREEMENT_DETAILS.ADE_STY_ID )
AND ( O_SERVICE_ELEMENTS.SEL_ID(+)=O_AGREEMENT_DETAILS.ADE_SEL_ID )
AND ( O_SERVICE_ELEMENTS.SEL_STY_CHILD_ID=sel_service_types.STY_ID(+) )
AND (
nvl(initcap(O_SERVICE_TYPES.STY_NAME),'NULL') = 'Home Care'
AND O_AGREEMENT_DETAILS.ADE_START_DATE <= '18-SEP-2005 ‘
AND (O_AGREEMENT_DETAILS.ADE_END_DATE >= '12-SEP-2005 ‘
OR O_AGREEMENT_DETAILS.ADE_END_DATE IS NULL )
AND ( nvl(O_AGREEMENT_DETAILS.ADE_CANCEL_FLAG,'O')='O' )
AND nvl(initcap(sel_service_types.STY_NAME),'NULL') NOT IN ('Lbc Dom/Laun 1hr +', 'Lbc H/C On Prescript', 'Lbc Personal Care', 'Lbc Rehab Homecare', 'Lbc Shop/Pen 1hr +', 'Live-In Carer', 'P&V Supported Living')
)
GROUP BY
nvl(O_SERVICE_PACKAGES.SPA_PER_GRO_ID,'NULL'),
nvl(olm_bo.get_address(O_SERVICE_PACKAGES.SPA_PER_GRO_IND, O_SERVICE_PACKAGES.SPA_PER_GRO_ID),'NULL'),
nvl(O_SERVICE_REGISTERS.SRE_OUN_ID,'NULL'),
O_AGREEMENT_DETAILS.ADE_WEEKLY_VISITS,
nvl(olm_bo.get_ref_desc(O_AGREEMENT_DETAILS.ADE_TIMEBAND,'TIME_BAND'),'NULL'),
nvl(initcap(sel_service_types.STY_NAME),'NULL')
|
|
|
|
|
Re: Help with Alias [message #188328 is a reply to message #188324] |
Fri, 18 August 2006 01:45  |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Maaher wrote on Fri, 18 August 2006 08:15 | I hope that o_agreement_details.ade_start_date and o_agreement_details.ade_end_date are varchar2 columns.
|
I'd, somehow, rather say that I hope that those are not VARCHAR2 but DATE columns, but WHERE clause condition should be rewritten in order to avoid possible misunderstandings; something like this:
AND o_agreement_details.ade_start_date <= TO_DATE('18.09.2005 ‘, 'dd.mm.yyyy')
|
|
|
Goto Forum:
Current Time: Wed Feb 12 17:38:59 CST 2025
|