UDA

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-14-2007
12:10 PM

09-14-2007
12:10 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-14-2007
03:46 PM

09-14-2007
03:46 PM

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

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