sql - DB2 restrict analytic function to subselect -

i encountered situation wanted restrict action of analytic function (or olap in db2) subset of present data. here example:

with my_table (      select 1 field1, 'a' field2,  1 field3, 'x' field4 dual      union      select 1 field1, 'a' field2, 2 field3, 'y' field4 dual      union      select 1 field1, 'b' field2, 3 field3, 'x' field4 dual      union      select 1 field1, 'a' field2, 4 field3, 'z' field4 dual      union      select 1 field1, 'b' field2, 5 field3, 'z' field4 dual      //... ) select first_value(field4) on (partition field1 order field3) field2_a my_table field2='a'  // need 'b', 'c' etc. 

now cumbersome required values of field2 , afterwards doing join or union, since constraints might more complicated.

thus want handle in single select. here trial:

//... select case when field2 = 'a'             first_value(field4)                        on (partition field1                              order case when field2='a' 0 else 1 end, field3)             else null end field4_a,        case when field2 = 'b'             first_value(field4)                        on (partition field1                              order case when field2='b' 0 else 1 end, field3)             else null end field4_b,       //a bit more complicated        case when field2 in ('a','c')             first_value(field4)                        on (partition field1                              order case when field2 in ('a','c') 0 else 1 end, field3)             else null end field4_ac my_table 

the idea order data set where-criterion, since there might cases rows such constraint not exist in partition (and unwanted result obtained), non-null value returned if field2 meets constraint (this done in outer case statement).

i guess approach works (--i haven't tested yet), bit detoured. are there easier or more direct approaches accomplish above task?

why doesn't want?

select first_value(field4) on (partition field1, field2 order field3) field2_a my_table 

if want pivoted separate columns:

select (case when field2 = 'a'              first_value(field4) on (partition field1, field2 order field3)          end) field4_a,        (case when field2 = 'b'              first_value(field4) on (partition field1, field2 order field3)          end) field4_b, 

edit:

i suspect want pivot "first" values. if can never null, might looking for:

select first_value(case when field2 = 'a' field4 end ignore nulls) on            (partition field1, field2 order field3) field4_a,        first_value(case when field2 = 'b' field4 end ignore nulls) on            (partition field1, field2 order field3) field4_b, . . .