03-13-2015 01:24 AM
I have a following query, which returns 636302 number of records:
create table application as
select distinct a.*, b.App1, b.App2, c.EMP1
from app1 a left join app2 b on (a.Application_Number=b.AA_No) left join app3 c on (a.Application_Number=c.APP_ID);
I tried rewriting the above query using merge as follows, but this query is returning 635610 number of records:
merge app1 (IN=a keep=_all_) app2 (in=b keep= App1 App2 AA_No rename=(AA_No=Application_Number))
app3 (in=c keep=EMP1 APP_ID rename=(APP_ID=Application_Number));
Can someone please tell me if I'm missing something above in the merge query because of which a difference of 692 records is coming ?
03-13-2015 02:22 AM
03-13-2015 02:31 AM
If you have 3 records for a key in dataset1, and 2 records for a key in dataset2, a merge in a data step by that key will give you 3 records, the join in SQL will give you 6 (cartesian product).