Hi,
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.
Best regards,
Hi,
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 .
I suggest you try a SAS passthru query. That should definitely match Oracle.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.