insert into ADWP_WORK1.Norm_Collateral_Fac
Collateral_Cd_Desc is null
Collateral_Cd_Desc in &invalid_collat_min.
) and (exposure>0 or book >0 or legal_book >0)
qualify row_number() over
(partition by pst_obligor_id,pst_fac_id,pst_sys_id,Maturity_date order by
period_dt desc,book desc, exposure desc, legal_book desc, GL_AU_NUM desc, GL_ACCOUNT_NUM desc)=1
execute(commit) by teradata;
from &output..FACILITY_COMBINED a,
and a.PST_SYS_ID =b.PST_SYS_ID
a.Collateral_Cd_Desc is null
a.Collateral_Cd_Desc in &invalid_collat_min.
execute (commit) by teradata;
I have a question on this query. It is my understanding that the bolded and underlined section starting with "qualify row_number" would group all the records by pst_obligor_id, pst_fac_id, pst_sys_id and Maturity_date and pick only one record based on the most recent period (If priod date is the same, move to book, then ecposure, etc.). Is this correct?
If it is correct, then how do I have 2 records with different COLLATERAL_CD fields? (Please refer to my jpg of data output)
Thank you very much.
Double check if the combination of (pst_obligor_id,pst_fac_id,pst_sys_id,Maturity_date) is actually the same for those rows, you didn't show all columns in the screen shot...