Desktop productivity for business analysts and programmers

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

Reply
Regular Contributor
Posts: 170

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

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;

 

Super User
Super User
Posts: 9,857

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

Super User
Posts: 10,599

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 170

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

Posted in reply to KurtBremser

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

Super User
Posts: 10,599

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


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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 4 replies
  • 553 views
  • 2 likes
  • 3 in conversation