Re: Forgot to ask....

If you don't want to repeat
...decode(ITF.ITEM_TYPE, 60 , 0, 61, 0, 62, 0 , 63, 0, 64, 0, 30 , 1...

or if you want flexibility the best way is to create your own order function as i said in my previous answer.

create or replace function my_order (val number) return number as
begin
  if ( val between 60 and 64 ) then

     return 0;
  elsif ( val between 30 and 32 ) then

     return 1;
  elsif val = 45 then

     return 2;
  elsif val = 90 then

     return 3;
  elsif val = 46 then

     return 4;
  else

     return 5;
  end if;
end;
/

and select ... from ... where ... order by my_order(ITF.ITEM_TYPE);

Regards
--
Michel

Unmesh <unmeshl_at_aol.com> a écrit dans le message : 38147A25.DBC81ABA_at_aol.com...
> What if the decode fuction need to be used with list of values using between
> operator?
> like say
> select ...
> from ...
> where ...
> order by decode(ITF.ITEM_TYPE, (between 60 and 64) , 0, (between 30 and 32) ,
1, 45,
> 2, 90, 3, 46, 4, 5);
>
> 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 - 11:21:01 CDT