topic Re: The execution of this query involves performing one or more Cartesian product joins that can not in SAS Enterprise Guide

The execution of this query involves performing one or more Cartesian product joins that can not be

alepage — Wed, 07 Mar 2018 13:04:04 GMT

Good morning,

I am debugging an existing code and after the execution of the code below, I receive this note:

The execution of this query involves performing one or more Cartesian product joins that cannot be optimized.

As I am not familiar at all with this note, I would like to know if there is something I could do to improve my code?

What are the implications of this note in term of result (created table)?

proc sql;
create table envoi_courriel_&yyyymm_e._&type_e._a as
select a.customer_number,
max(case when a.lob = "AUTO" then 1 else 0 end) as flg_quote_auto,
max(case when a.lob = "HOME" then 1 else 0 end) as flg_quote_hab,
max(case when a.flg_vip = 1 then 1 else 0 end) as flg_vip,
max(a.flg_lo) as flg_locataire,
max(a.principal_operation_date) as date_last_quote format YYMMDD10.

from commun.quotes_uniques_eis a
left join work.customer_email_info b
on a.customer_number = b.customernumber
left join commun.envoi_courriel_dates (where=( today=&today.)) c
on c.today=&today;

quit;

Re: The execution of this query involves performing one or more Cartesian product joins that can not

RW9 — Wed, 07 Mar 2018 13:13:05 GMT

There are a lot of posts and guidance out there on this topic:

https://communities.sas.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=false&q=Cartesian+product+join

http://www.orafaq.com/wiki/Cartesian_join

Re: The execution of this query involves performing one or more Cartesian product joins that can not

Kurt_Bremser — Wed, 07 Mar 2018 13:29:14 GMT

You are using SQL summary functions without a group by, so SAS has to create the values for the whole result of the join first and then re-read and assign for every customer_number.

Could it be that

group by a.customer_number

is missing from the code?

Since you do not use any variables from relations b and c, and a left join will always keep all observations from a, I don't see why the joins are there anyway.

Re: The execution of this query involves performing one or more Cartesian product joins that can not

alepage — Wed, 14 Mar 2018 12:11:24 GMT

Good Morning Kurt,

Thanks a lot for your advice. After many test, I have which join is causing this note. It is the last join because there is no real key to make the join.

Anyway, I will have to rewrite this program completely because there are too many notes generated and I felt that this code is not as clear as I would like to.

Regards,

from commun.quotes_uniques_eis a
left join work.customer_email_info b
on a.customer_number = b.customernumber
left join (select * from commun.envoi_courriel_dates1 where today=&today.) c on c.today=&today

Re: The execution of this query involves performing one or more Cartesian product joins that can not

Kurt_Bremser — Wed, 14 Mar 2018 12:28:50 GMT

@alepage wrote:

Anyway, I will have to rewrite this program completely because there are too many notes generated and I felt that this code is not as clear as I would like to.

Kudos to this.