Rewriting the Self join

Database
KVB
Enthusiast

Rewriting the Self join

MERGE_ID is the primary index in  CUST_HIST

SELECT * FROM

(SEL A.MERGE_ID,A.REM_ID, A.EFF_DT, A.FIRST_MERGE_DT , A.CURR_CUST_MERGE_DT FROM

CUST_HIST A

INNER JOIN

CUST_HIST B ON A.REM_ID=B.MERGE_ID

UNION ALL

SEL B.MERGE_ID,B.REM_ID, B.EFF_DT, B.FIRST_MERGE_DT , B.CURR_CUST_MERGE_DT FROM

CUST_HIST A

INNER JOIN

CUST_HIST B ON A.REM_ID=B.MERGE_ID )A

GROUP BY 1,2,3,4,5

Is there any other better way to rewrite this.Like duplicating the table with REM_ID as PI.Or else this is fine?

Regards

Bikky

4 REPLIES
Enthusiast

Re: Rewriting the Self join

Hi Bikky,

My thought is that the first select  within the derive is enough.Do I miss anything here, due to slight astigmatism :) ? Why don't your see the explain and validate the data too?

Cheers,

Raja

Supporter

Re: Rewriting the Self join

hm, try and check the differences in explain and run times

select A.MERGE_ID,
A.REM_ID,
A.EFF_DT,
A.FIRST_MERGE_DT ,
A.CURR_CUST_MERGE_DT
FROM CUST_HIST A
where exists ( select *
from CUST_HIST B
where A.REM_ID=B.MERGE_ID
)
UNION -- this would make the group by obsolate
select A.MERGE_ID,
A.REM_ID,
A.EFF_DT,
A.FIRST_MERGE_DT ,
A.CURR_CUST_MERGE_DT
FROM CUST_HIST A
where exists ( select *
from CUST_HIST B
where B.REM_ID=A.MERGE_ID
)
Supporter

Re: Rewriting the Self join

This should create also the same output an would do the self join only once but comes with a smal prod join

SELECT
case when c.id = 1 then A.MERGE_ID else B.REM_ID end,
case when c.id = 1 then A.REM_ID else B.REM_ID end,
case when c.id = 1 then A.EFF_DT else B.EFF_DT end,
case when c.id = 1 then A.FIRST_MERGE_DT else B.FIRST_MERGE_DT end ,
case when c.id = 1 then A.CURR_CUST_MERGE_DT else b.CURR_CUST_MERGE_DT end
FROM CUST_HIST A
INNER JOIN
CUST_HIST B
ON A.REM_ID=B.MERGE_ID
CROSS JOIN
(select calendar_date - current_date as id from sys_calendar.calendar where id in (1,2)) as c
group by 1,2,3,4,5
ih
N/A

Re: Rewriting the Self join

Hi all,

https://www.periscope.io/blog/use-self-joins-to-calculate-your-retention-churn-and-reactivation-metr...

is there a terada sql assistant equivalent to this query as I really need it to calculate the churn and re-engagement. I would be very grateful for guidance as I am new to teradata. I am not able to show my query since it contains confidential data, but there are a few sub-queries based on multiple joins.

Many thanks in advance