Question on SAS in Teradata

General

Question on SAS in Teradata

execute(
insert into ADWP_WORK1.Norm_Collateral_Fac
(pst_obligor_id,pst_fac_id,pst_sys_id,Collateral_Cd,Collateral_Cd_Desc,Maturity_date)
select pst_obligor_id,pst_fac_id,pst_sys_id,Collateral_Cd,Collateral_Cd_Desc,Maturity_date
from
&output..FACILITY_COMBINED
where not
(
Collateral_Cd_Desc is null
or
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

;
)by teradata;
execute(commit) by teradata;
execute(
update a
from &output..FACILITY_COMBINED a,
ADWP_WORK1.Norm_Collateral_Fac b
set Collateral_Cd=b.Collateral_Cd,Collateral_Cd_Desc=b.Collateral_Cd_Desc
where
a.PST_OBLIGOR_ID=b.PST_OBLIGOR_ID
and a.PST_FAC_ID=b.PST_FAC_ID
and a.PST_SYS_ID =b.PST_SYS_ID
and a.Maturity_date=b.Maturity_date
and
(
a.Collateral_Cd_Desc is null
or
a.Collateral_Cd_Desc in &invalid_collat_min.
)
;
)by teradata;
execute (commit) by teradata;

Hello,

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.

Tags (2)
1 REPLY
Junior Contributor

Re: Question on SAS in Teradata

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