Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Forgot to ask....
What if the decode fuction need to be used with list of values using between
operator?
like say
select ... from ... where ...
Any feedback is appreciated..
Thanks
Unmesh
Unmesh wrote:
> Many Thanks Mike...
>
> Have a nice day.
>
> Unmesh
>
> Michel Cadot wrote:
>
> > If you know all the values and the order you can use the decode function:
> >
> > select ...
> > from ...
> > where ...
> > order by decode(ITF.ITEM_TYPE, 60, 0, 30, 1, 45, 2, 90, 3, 46, 4, 5);
> >
> > If you don't know this order but have to calculate it, you can create a
> > function:
> >
> > create or replace function my_order (val ITF.ITEM_TYPE%type) return number
> > as
> > ord number;
> > begin
> > /* calculate an order value, for example: */
> > ord := cos (val * 3.14159265359 / 180);
> > return ord;
> > end;
> > /
> >
> > And then:
> >
> > select ...
> > from ...
> > where ...
> > order by my_order(ITF.ITEM_TYPE);
> >
> > --
> > Have a nice day
> > Michel
> >
> > Unmesh <unmeshl_at_aol.com> a écrit dans le message : 38146C76.33C14E7F_at_aol.com...
> > > Hi all,
> > > I have a problem with the 'order by' clause in SQL. I have a query like
> > >
> > > select
> > > AWD.ITEM_TYPE,
> > > AWD.EMPLID,
> > > AWD.INSTITUTION
> > > FROM PS_STDNT_AWARDS AWD,
> > > PS_ITEM_TYPE_FA ITF
> > > WHERE AWD.EMPLID = 'XXXXX'
> > > AND AWD.INSTITUTION = 'YYYYY'
> > > AND AWD.AID_YEAR = '2000'
> > > AND ((AWD.AWARD_PERIOD = 'A') OR (AWD.AWARD_PERIOD = 'B'))
> > > AND ((AWD.AWARD_STATUS = 'A') OR (AWD.AWARD_STATUS = 'O'))
> > > AND ITF.PRINT_LTR_OPTION <> 'N'
> > > AND ITF.SETID = AWD.SETID
> > > AND ITF.AID_YEAR = '2000'
> > > AND ITF.ITEM_TYPE = AWD.ITEM_TYPE
> > > and itf.item_type in ('60', '30', '45', '90', '46')
> > > AND ITF.EFFDT = (SELECT MAX(EFFDT)
> > > FROM PS_ITEM_TYPE_FA ITF1
> > > WHERE ITF1.SETID = ITF.SETID
> > > AND ITF1.AID_YEAR = ITF.AID_YEAR
> > > AND ITF1.ITEM_TYPE = ITF.ITEM_TYPE
> > > AND ITF1.EFF_STATUS = 'A'
> > > AND ITF1.EFFDT <= sysdate)
> > > ORDER BY ITF.ITEM_TYPE
> > >
> > >
> > > where I want the output to come in a particular order of item types.
> > > Like
> > > 60, 30, 45, 90, 46.
> > >
> > > i.e. I want to produce the output according to a particular order in a
> > > list of values. Which is the way to do this? Any feedback is
> > > appreciated.
> > >
> > > Thanks,
> > > Unmesh
> > >
Received on Mon Oct 25 1999 - 10:41:25 CDT