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 -> Making First_value then second value in a recordset within case statement

Making First_value then second value in a recordset within case statement

From: MrHelpMe <clinttoris_at_hotmail.com>
Date: 19 Feb 2007 16:13:15 -0800
Message-ID: <1171930395.920182.152900@q2g2000cwa.googlegroups.com>


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. Received on Mon Feb 19 2007 - 18:13:15 CST

Original text of this message

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