3 weeks ago - last edited 3 weeks ago
Here is the query in Oracle SQL:
select s.OWNER_CUSTOMER_KEY, s.SUBSCRIPTION_KEY, rap.COMM_SHAPE_ID, rap.RES_APP_INDICATOR_ID from SUBSCRIPTION_DIM s left join MASTER_CUSTOMER mc on mc.KURT_ID = s.USER_CUSTOMER_KEY and s.DRM_MARKET_PRODUCT_GROUP = 'Mobil Tale' left join RES_AND_APPROVAL rap on rap.MASTER_ID = mc.MASTER_ID and rap.comm_shape_id in (3,4) and rap.RES_APP_VALID_TO_DATE is null
This one reruns 4 160 298 rows.
SAS DI job with join transformation returns ridiculous 227 million rows.
proc sql; create table work.W5JS0I6C as select SUBSCRIPTION_DIM.OWNER_CUSTOMER_KEY length = 8 label = 'OWNER_CUSTOMER_KEY', SUBSCRIPTION_DIM.SUBSCRIPTION_KEY length = 8 label = 'SUBSCRIPTION_KEY', RES_AND_APPROVAL.COMM_SHAPE_ID length = 8 label = 'COMM_SHAPE_ID', RES_AND_APPROVAL.RES_APP_INDICATOR_ID length = 8 label = 'RES_APP_INDICATOR_ID' from ciccm.SUBSCRIPTION_DIM as SUBSCRIPTION_DIM left join CDC.MASTER_CUSTOMER as MASTER_CUSTOMER on ( MASTER_CUSTOMER.KURT_ID = SUBSCRIPTION_DIM.USER_CUSTOMER_KEY and SUBSCRIPTION_DIM.DRM_MARKET_PRODUCT_GROUP = 'Mobil Tale' ) left join CDC.RES_AND_APPROVAL as RES_AND_APPROVAL on ( RES_AND_APPROVAL.MASTER_ID = MASTER_CUSTOMER.MASTER_ID and RES_AND_APPROVAL.COMM_SHAPE_ID IN (3,4) and RES_AND_APPROVAL.RES_APP_VALID_TO_DATE = . ) ; quit;
I would say I am doing something very wrong here, I just don't see it.
I tried to variate DI job: extract subset from source tables first to make join "lighter", but regardless I get huge and wrong result data set.
Interesting is that when I run join on only first 2 tables, result is correct.
When I include 3rd one in this case, everything goes down the drain.
Any hint is more than welcomed.
3 weeks ago
2 weeks ago
Thanks for the hint.
I removed scheme names from Oracle SQL for things to be clearer.
In both cases, the same schemes are accessed.
I checked the log with those options turned ON (attached). SAS accesses two Oracle schemas as two different users, if that makes any difference.
I didn't find anything that would point the cause of the problem in that log.
However, I did try to do an extract to work tables first (only subset of original oracle tables that will be used in a join later. The result was exactly the same .
2 weeks ago