oracle 9.2 and using Oracle SQL Developer 1

[LadyDi02]

Hello all,

I am using oracle 9.2 and using Oracle SQL Developer

I have a case statement as such

(CASE WHEN a.name= first_value(a.name IGNORE NULLS) OVER (Partition by a.date order by a.ID) and a.state= 'done' and a.listinggr IN ('XXX', 'BBB') then 1
           WHEN a.name= first_value(a.name IGNORE NULLS) OVER (Partition by a.date order by a.id) and a.state= 'done' and a.listinggr NOT IN ('XXX', 'BBB') then First_value = the value where a.listinggr = XXX or BBB and if this is true give this a 1           
        else 0 END) Pleasehelp

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.statein XXX or BBB 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 each a.id 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 perform other case statements and all records are needed.

 

[Dagon]

I'm not sure I understand what you want to do, but your approach doesn't look quite right. You should obtain the first_value in an inline view and then access it like a regular column e.g.

select 
case when name = first_val and state= 'done' and 
          listinggr IN ('XXX', 'BBB') then 1 
     when name = first_val and state= 'done' and 
          listinggr NOT IN ('XXX', 'BBB') then
          -- didn't understand the rest of your conditions
     end
from
(select 
first_value(a.name IGNORE NULLS) OVER (Partition by a.date order by a.ID) first_val,
state,
listinggr,
name
from table)

 

[LadyDi02]

Dagon,

Thanks for your response.

Sorry for not being clear. Maybe if I show you some data it would make more sense. Say I have the following
Name State Listinggr Count
Lou Exit BBB 0
Chris Not Done BBB 0
John Done BBB 1

Fred Not Done XXX 0
Jay Done BBB 1

What I am trying to do is have the query look at each record..so looking at the first record(which is the First_value you will see Lou, Exit and BBB. Well according to what I am trying to achieve in my case statement, Lou Exit and BBB is not what I am looking for so this should not be the first_value. The system will ignore this record and then continue to search down the list of records. The next record, Chris, Not Done and BBB is also not what I am looking for in my case statement so this will get a Zero also and the query will continue. The next record John, Done, BBB is what I am looking for so the query would consider this the First_value and then it will look at the case statement and it will see it met the criteria so it would give this record a 1. Then continue with the next sets of records Jay Done and BBB should be the first record in this partition. Basically I am just trying to change the position of the First_value function in oracle.

My whole query looks something like

Select Name,
State,
Listinggr,
Case Statement 1,
Case Statement 2,
Case Statement 3
From table
Order by name, state

----something along these lines is what my query looks like so I'm not really understanding your inline view. Could you explain your inline view based on what I have mentioned. Thanks again Dagon.

 

[Dagon]

Are you just trying to flag which record is the first one in the set ? If so, you would be better off using a ranking function:

select 
state,
listinggr,
name,
case when rnk = 1 then 1 else null end as first_record
from
(select 
rank() OVER (Partition by a.date order by case when name is null then 0 else 1 end, a.ID) rnk,
state,
listinggr,
name
from table)

I've rarely found first_value to be particularly useful and nearly always end up changing any SQL I write using it to use RANK or ROW_NUMBER instead.

 

[LadyDi02]

Dagon,

That is exactly what I am looking for. I should have mentioned that I thought I should use the rank but decided to go with First_value. Thanks so much for this. Saved me some valuable time.