Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle SQL problem... Newbie question

Re: Oracle SQL problem... Newbie question

From: Unmesh <unmeshl_at_aol.com>
Date: Mon, 25 Oct 1999 11:14:26 -0400
Message-ID: <381473D1.54CB7BFF@aol.com>


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:14:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US