Help in Ordered Analytic Functions

Database

Help in Ordered Analytic Functions

Code:

select subscr_id, eff_from_dt ,eff_to_dt ,typ
from
( select subscr_id ,typ ,cod1
,min(eff_from_dt) as eff_from_dt
,max(eff_to_dt) as eff_to_dt
from
( sel subscr_id ,typ ,eff_from_dt ,eff_to_dt
,case when cod = 0
then max(cod) over (partition by subscr_id,typ
order by eff_from_dt
rows unbounded preceding)
else cod
end as cod1
from
( SELECT Subscr_ID, typ, Eff_From_Dt, Eff_To_Dt,
Rank() OVER (ORDER BY subscr_id,eff_from_dt ) as rk,
CASE WHEN COALESCE(MAX(typ) OVER (PARTITION BY Subscr_ID
ORDER BY Eff_From_Dt
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) <> typ
THEN rk
ELSE 0
END AS Cod FROM tableA
)a
)b
group by 1,2,3
)c

Dear Experts,

Please help in having the optimal code. The attached picture has the rows of table A and the desired output. I have given in the code snippet which the code i have written to get the output.Not sure whether there is any other easy way to write this query. Please have a look and suggest the best method to do this . Thanks in advance for your valuable time.

3 REPLIES
Supporter

Re: Help in Ordered Analytic Functions

On which version of TD are you?

Check if you can use the TD_NORMALIZE_... functions 

they should do what you want.

Source for information: SQL Functions, Operators, Expressions, and Predicates 

Re: Help in Ordered Analytic Functions

Thanks for the response Ulrich .The version is TD 14.10 and i am hearing the first time about the TD_NORMALIZE functions :-). The functions are compatible in 14.10 and will try out them and update here.

Re: Help in Ordered Analytic Functions

Rewrote

sel subscr_id,typ,eff_from_dt,eff_to_dt from
(sel subscr_id, typ
,min(eff_from_dt) over (partition by subscr_id,typ order by eff_from_dt
reset when COALESCE(MAX(Typ) over
(partition by subscr_id order by eff_from_dt rows 1 preceding), '99999999999') <> typ) as eff_from_dt
,max(eff_to_dt) over (partition by subscr_id,typ order by eff_from_dt
reset when COALESCE(MAX(Typ) over
(partition by subscr_id order by eff_from_dt rows 1 preceding) , '99999999999')<> typ) as eff_to_dt
from preced1 ) a group by 1,2,3,4

using reset clause. still have to explore the normalize functions.