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: dean <deanbrown3d_at_yahoo.com>
Date: 20 Feb 2007 20:59:15 -0800
Message-ID: <1172033955.822891.27920@q2g2000cwa.googlegroups.com>


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. Received on Tue Feb 20 2007 - 22:59:15 CST

Original text of this message

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