Experts i'm in desparate need please..using oracle 9.2

I have a case statement as such
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.

On Feb 19, 7:13 pm, "MrHelpMe" <clintto...@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
>

>
> 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.

On Feb 20, 11:59 pm, "dean" <deanbrow...@yahoo.com> wrote:
> On Feb 19, 7:13 pm, "MrHelpMe" <clintto...@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
> >

>
> > 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.