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

[alepage]

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;

[RW9]

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&...

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

[Kurt_Bremser]

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.

[alepage]

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

[Kurt_Bremser]

---

@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.