Re: Oracle SQL problem... Newbie question

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Mon, 25 Oct 1999 16:59:04 +0200
Message-ID: <7v1raf$p34$1@oceanite.cybercable.fr>

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 - 09:59:04 CDT