inner join with or in the join

UDA
N/A

inner join with or in the join

Hello

I have a table with lines containing multiple dates looking like the following table

Example :

Client Subscriptiondate Paymentdate StartDate Product
1 01.01.2007 01.05.2007 01.06.2007 AAA

and I would like to count the number of subscription, Payment, etc for each day month etc etc
so the idea is to build a view giving me this result :

Client date Payment Start Subscription Product
1 01.01.2007 0 0 1 AAA
1 01.05.2007 1 0 0 AAA
1 01.06.2007 0 1 1 AAA

and my query looks like

select
select Client, count(Subscriptiondate), count(Paymentdate), Count(StartDate), Product
from mytable inner join Sys_Calendar.CALENDAR
on Subscriptiondate = CalendarDate OR
Paymentdate = CalendarDate OR
StartDate = CalendarDate

But of course that kind of queries are very slow (the full query uses around 5 tables with millions rows in each)

Any ideas to optimize that, or any way I do that?

Thank you

Paul

1 REPLY
N/A

Re: inner join with or in the join

Hi Paul,
you have to get rid of the OR, because OR-ed join conditions lead to product joins in Teradata.

There are two common ways to solve that problem:

UNIONs/CASE:
select Client, mydate,
count(case when x = 1 then 1 end) as Payment,
count(case when x = 2 then 1 end) as Start,
count(case when x = 3 then 1 end) as Subscription,
from
(select 1 as x, Paymentdate from mytable
union all
select 2 as x, StartDate from mytable
union all
select 3 as x, Subscriptiondate from mytable
) dt
group by 1,2

or CROSS JOIN/CASE:

create volatile table dummy(x byteint) on commit preserve rows;
insert into dummy(1);
insert into dummy(2);
insert into dummy(3);

select Client,
case
when x = 1 then PaymentDate
when x = 2 then StartDate
when x = 1 then SubscriptionDate
end as mydate,
count(case when x = 1 then 1 end) as Payment,
count(case when x = 2 then 1 end) as Start,
count(case when x = 3 then 1 end) as Subscription,
from mytable cross join dummy
group by 1,2

Dieter