Enterprise Guide does not merge tables as intended

deleted_user — Wed, 20 Dec 2006 14:13:30 GMT

I am trying to join two tables. One is the main data table and the other (from which I need one field), is relatively small. I am joining using a Left Join on two fields in the main table and three fields in the secondary table. To do this properly in Proc SQL, I would manually code this as follows:-
PROC SQL;
CREATE TABLE WORK.Query_for_CLGLEXT AS SELECT CLGLEXT.account_code,
CLGLEXT.accounting_date FORMAT=DDMMYY10.,
CLGLEXT.policy_number,
CLGLEXT.amount,
CLGLEXT.narrative,
CLGLEXT.generation_date FORMAT=DDMMYY10.,
CLGLEXT.class_code,
CLGLEXT.transaction_code,
CLGLEXT.batch_id,
CLGLEXT.cr_acc_no,
CLGLEXT.dr_acc_no,
CLGLEXT.agent,
CLGLEXT.last_acc_date FORMAT=DDMMYY10.,
CLGLEXT.renewal_date FORMAT=DDMMYY10.,
CLGLEXT.basic_prem_prop,
CLGLEXT.levy,
CLGLEXT.sfile,
CLASSIC_MAPPINGS.'GL Code'n
FROM CL_GLEXT.CLGLEXT AS CLGLEXT
LEFT JOIN CLGLPOST.CLASSIC_MAPPINGS AS CLASSIC_MAPPINGS ON
(CLGLEXT.account_code = CLASSIC_MAPPINGS.'Source Account'n) AND
(CLGLEXT.accounting_date >= CLASSIC_MAPPINGS.'Start Date'n) AND
(CLGLEXT.accounting_date <= CLASSIC_MAPPINGS.'End Date'n)
AND CLASSIC_MAPPINGS.Enabled = "*";
QUIT;

However, using the EG GUI, I am only able to do the following:-

PROC SQL;
CREATE TABLE WORK.Query_for_CLGLEXT AS SELECT CLGLEXT.account_code,
CLGLEXT.accounting_date FORMAT=DDMMYY10.,
CLGLEXT.policy_number,
CLGLEXT.amount,
CLGLEXT.narrative,
CLGLEXT.generation_date FORMAT=DDMMYY10.,
CLGLEXT.class_code,
CLGLEXT.transaction_code,
CLGLEXT.batch_id,
CLGLEXT.cr_acc_no,
CLGLEXT.dr_acc_no,
CLGLEXT.agent,
CLGLEXT.last_acc_date FORMAT=DDMMYY10.,
CLGLEXT.renewal_date FORMAT=DDMMYY10.,
CLGLEXT.basic_prem_prop,
CLGLEXT.levy,
CLGLEXT.sfile,
CLASSIC_MAPPINGS.'GL Code'n
FROM CL_GLEXT.CLGLEXT AS CLGLEXT
LEFT JOIN CLGLPOST.CLASSIC_MAPPINGS AS CLASSIC_MAPPINGS ON (CLGLEXT.account_code = CLASSIC_MAPPINGS.'Source Account'n) AND (CLGLEXT.accounting_date >= CLASSIC_MAPPINGS.'Start Date'n) AND (CLGLEXT.accounting_date <= CLASSIC_MAPPINGS.'End Date'n)
WHERE CLASSIC_MAPPINGS.Enabled = "*";
QUIT;

Why can't the GUI implement the additional test on the left join? I cannot see where to enable the condition that can be achieved in the manual version. Can anyone point me in the right direction, or can SAS take this as a bug in EG4.1?

Re: Enterprise Guide does not merge tables as intended

DaveShea — Wed, 03 Jan 2007 09:32:27 GMT

Chris,

I am not sure if I am seeing the same problem that you do. The only difference I see in your two pieces of SQL are that your manually coded version contains the:

CLASSIC_MAPPINGS.Enabled = "*"

selection criterion as part of the ON whereas in the version generated by SAS/EG the same criterion is included as part of a stand-alone WHERE clause following the ON.

I would guess that in this case your actual results would be identical. Is this not the case for you ? If your results are not the same, what are the differences ?

Cheers.

Down-Under Dave.

topic Enterprise Guide does not merge tables as intended in SAS Enterprise Guide

Enterprise Guide does not merge tables as intended

Re: Enterprise Guide does not merge tables as intended