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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Making First_value then second value in a recordset within case statement

Re: Making First_value then second value in a recordset within case statement

From: MrHelpMe <clinttoris_at_hotmail.com>
Date: 21 Feb 2007 06:47:32 -0800
Message-ID: <1172069252.095508.298730@q2g2000cwa.googlegroups.com>


On Feb 20, 11:59 pm, "dean" <deanbrow..._at_yahoo.com> wrote:
> On Feb 19, 7:13 pm, "MrHelpMe" <clintto..._at_hotmail.com> wrote:
>
>
>
>
>
> > Experts i'm in desparate need please..using oracle 9.2
>
> > I have a case statement as such
> > [code]
> > (CASE WHEN a.field1= first_value(a.field1 IGNORE NULLS) OVER
> > (Partition by a.field2 order by a.field3) and a.field4= 'WIP' and
> > a.field5 IN ('Homes', 'Autos') then 1
> > WHEN a.field1= first_value(a.field1 IGNORE NULLS) OVER
> > (Partition by a.field2 order by a.field3) and a.field4= 'WIP' and
> > a.field5 NOT IN ('Homes', 'Autos') then First_value = the value where
> > a.field5 = Homes or Autos and if this is true give this a 1
> > else 0 END) TEST
> > [/code]
>
> > What I am trying to do is look at each record one at a time and based
> > on the recordset if the first value has a.field5 in Homes or auto then
> > examine that record and see if it meets all the rest of the criteria
> > that I put in my case statement and if it does then give me a 1. If
> > it doesn't keeping moving down the recordset for that a.field2 and
> > find the record that meets the criteria and make that the first value.
> > I am trying to ignore all records(not remove) that don't have the
> > appropriate field value. Is this possible? I do not want to remove
> > these records as I need to preform other case statements and all
> > records are needed.
>
> Your question is a little muddled - what do you mean by " keeping
> moving down the recordset for that a.field2 "? Please clarify.- Hide quoted text -
>
> - Show quoted text -

Hey Dean,

Thanks for the feedback. I just figured it out. What I was trying to do is find a position in recordset. I used the Rank() function and accomplished what I needed. Thanks for the reply. Received on Wed Feb 21 2007 - 08:47:32 CST

Original text of this message

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