Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Making First_value then second value in a recordset within case statement
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