All Forums Database
Niesh20us 78 posts Joined 06/13
25 Feb 2014
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.

Niesh20us 78 posts Joined 06/13
25 Feb 2014

Can someone please help?

M.Saeed Khurram 544 posts Joined 09/12
25 Feb 2014

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

Adeel Chaudhry 773 posts Joined 04/08
25 Feb 2014

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.

-- If you are stuck at something .... consider it an opportunity to think anew.

LUCAS 56 posts Joined 06/09
27 Mar 2014

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

You must sign in to leave a comment.