Home » SQL & PL/SQL » SQL & PL/SQL » Help with Alias
Help with Alias [message #188228] Thu, 17 August 2006 08:32 Go to next message
aadebayo
Messages: 35
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 #188248 is a reply to message #188228] Thu, 17 August 2006 10:21 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
If i am not mistaken aliases cannot be used in where clause.
Re: Help with Alias [message #188324 is a reply to message #188248] Fri, 18 August 2006 01:15 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I hope that o_agreement_details.ade_start_date and o_agreement_details.ade_end_date are varchar2 columns. Now as for this "alias". But Yasir is right: column aliases cannot be used in the same select where they are used. If you would wrap it in another select, you can:
SELECT summary
     , ...
FROM  ( <your_select_here> )
WHERE summary = <whatever>
The alternative is to use the calculation in the where clause.

MHE
Re: Help with Alias [message #188328 is a reply to message #188324] Fri, 18 August 2006 01:45 Go to previous message
Littlefoot
Messages: 20901
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')
Previous Topic: DBMS_OUTPUT.ENABLE (500000);
Next Topic: Parent child relationship table
Goto Forum:
  


Current Time: Thu Dec 08 14:04:10 CST 2016

Total time taken to generate the page: 0.29838 seconds