SQL Issue - Qualify partition by issue

Database
Enthusiast

SQL Issue - Qualify partition by issue

Hi Experts,

I have a situation where i have to create volatile table of the unique combination of accountid and sor_id but even after adding partition by condition in the below table its giving me duplicate rows. Actually PEDW_SECURED.OLB_PRFL has ACCOUNTID as varchar(40) so data is like 

AccountID                          SORID

1234567                            11

00001234567                    11

But while i am creating the table i am converting to decimal but its still giving me duplicate rows like below

ACCOUNTID                  SORID                      EMAIL

12345                            11                         ABC@ABC.COM

12345                            11                         ABC@ABC.COM

CREATE VOLATILE SET  TABLE OLB_CUST_SCL1  AS 

(

SELECT  CAST ( C.ACCOUNTID as DECIMAL(18,0)) AS ACCOUNTID ,  C.SOR_ID , C.EMAIL 

FROM  PEDW_SECURED.OLB_PRFL C

INNER JOIN SECURED.BANK_CROSS_REFNC B

ON CAST( TRIM(LEADING '0' FROM TRIM(C.ACCOUNTID)) AS VARCHAR(18) ) = B.ACCT_NUM

AND C.SOR_ID=B.SOR_ID

INNER JOIN PEDW_SECURED.EC_CUST_SUMRY A

ON B.ACCT_ID=A.ACCT_ID

AND B.SOR_ID=A.SOR_ID

AND A.CUST_ACCT_ROLE_TYPE_CD='PR'

WHERE C.SOR_ID IN (12,13,16,17,38,39,56)

AND C.EMAIL IS NOT NULL

QUALIFY ROW_NUMBER() OVER(PARTITION BY C.ACCOUNTID , C.SOR_ID  ORDER BY TIMELASTSUCCESSFULLOGIN DESC ) = 1)

WITH DATA  PRIMARY INDEX(ACCOUNTID, SOR_ID) ON COMMIT PRESERVE ROWS  ;

Please help me how should i get the unique combination of accountid and sorid.

4 REPLIES
Enthusiast

Re: SQL Issue - Qualify partition by issue

Can someone please help?

Enthusiast

Re: SQL Issue - Qualify partition by issue

Try this:

CREATE VOLATILE SET  TABLE OLB_CUST_SCL1  AS 

(

SELECT CAST ( C.ACCOUNTID as DECIMAL(18,0)) AS ACCOUNTID , C.SOR_ID , C.EMAIL

FROM PEDW_SECURED.OLB_PRFL C

INNER JOIN SECURED.BANK_CROSS_REFNC B

ON CAST( TRIM(LEADING '0' FROM TRIM(C.ACCOUNTID)) AS VARCHAR(18) ) = B.ACCT_NUM

AND C.SOR_ID=B.SOR_ID

INNER JOIN PEDW_SECURED.EC_CUST_SUMRY A

ON B.ACCT_ID=A.ACCT_ID

AND B.SOR_ID=A.SOR_ID

AND A.CUST_ACCT_ROLE_TYPE_CD='PR'

WHERE C.SOR_ID IN (12,13,16,17,38,39,56)

AND C.EMAIL IS NOT NULL

QUALIFY ROW_NUMBER() OVER(PARTITION BY CAST ( C.ACCOUNTID as DECIMAL(18,0)) , C.SOR_ID ORDER BY TIMELASTSUCCESSFULLOGIN DESC ) = 1)

WITH DATA PRIMARY INDEX(ACCOUNTID, SOR_ID) ON COMMIT PRESERVE ROWS ;
Khurram
Teradata Employee

Re: SQL Issue - Qualify partition by issue

What you are doing in your code is .... you are casting ACCOUNTID in select for it to be DECIMAL .... but you are not converting is to DECIMAL in your PARTITION BY statement. Hence you will get both sample values as output.

Putting the same casting in PARTITION BY should resolve the issue.

Enthusiast

Re: SQL Issue - Qualify partition by issue

Hi Adeel,

i just experiment the same issue on Acctg table (V14.10): rows are multiplied by the number of AMPs !!!

SELECT

 AccountName

,USERNAME

,SUM(CPUNorm)  over (partition by ACCOUNTNAME, USERNAME)     (NAMED "CPU_sec")

,SUM(CPUNorm)  over (partition by ACCOUNTNAME)     (NAMED "CPU_sec Acct")

,SUM(IO) over (partition by ACCOUNTNAME, USERNAME) (named "I/O" )

,SUM(IO) over (partition by ACCOUNTNAME) (named "I/O  Acct")

FROM DBC.ACCTG;

The expected answer is (partially) delivered by a good old GROUP BY Accountname, Username

Pierre