BookmarkSubscribeRSS Feed
strsljen
Obsidian | Level 7

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,

--
Mario
4 REPLIES 4
LinusH
Tourmaline | Level 20
If you only moved the query, add
Options sastrace = ',, d' sastraceloc = saslog nostsuffix;
You'll get the what the query transform to in Oracle.
Your Oracle query seems to accessing the same schema, but in SAS you have two libnames? Shouldn't change the logic though, just being curious...
Data never sleeps
strsljen
Obsidian | Level 7

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 .

 

 

--
Mario
SASKiwi
PROC Star

I suggest you try a SAS passthru query. That should definitely match Oracle.

LinusH
Tourmaline | Level 20
Oh that's a mouthful...
Sorry my fingers stumbling on my phone, sastrace should read
',,,d'
Which hopefully give a nicer log...
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 4 replies
  • 1005 views
  • 0 likes
  • 3 in conversation