DATA Step, Macro, Functions and more

SQL Pass-Thru vs Proc SQL - Same query has different results

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

SQL Pass-Thru vs Proc SQL - Same query has different results

[ Edited ]

Hi. I've had to change some code from SQL Pass-Thru to Proc SQL and while the Where conditions haven't changed the number of records being pulled has changed to an incorrect number.  

 

SQL Pass-Thru is pulling directly from the Oracle tables where ACTUAL_DLVRY_DATE is defined as a Date type.  Whereas the Proc SQL is referencing an Oracle Libname instead.  Can anyone identify what is going wrong and how it can be corrected?

 

Any help would be greatly appreciated!

 

 

This SQL Pass-Thru code pulls the correct number of records at 341.

 

	proc sql;
		connect to oracle as db (user=&orauser password=&orapass path="ivasprd");
		create table QueryData as 
		select * from connection to db
		(   select b.imb_code
	            from ivprl.bi_spm_piece_iv_recon a,  ivprl.bi_spm_piece_bids_recon b                                                                                                                                                                                                                                                                                                                                                                                                                                                   
	            where a.imb_code = b.imb_code
	            and A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL
		); 
		disconnect from db;
	quit;

But this Proc SQL code pulls the wrong number of records at 29

 

libname iv_ora oracle user=&orauser pass=&orapass path="IVASPRD" schema="IVPRL";

proc sql; create table QueryData%sysfunc(tranwrd(&rule_order,.,_)) as select b.imb_code length = 31 from iv_ora.bi_spm_piece_iv_recon a, iv_ora.bi_spm_piece_bids_recon b where a.imb_code = b.imb_code and A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL; quit;

 

 

 


Accepted Solutions
Solution
‎05-26-2016 11:49 AM
Super User
Posts: 5,429

Re: SQL Pass-Thru vs Proc SQL - Same query has different results

Posted in reply to buechler66

It probably has to do with the use of NULL.

Try

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;

which will return how SAS/ACCESS libname will translate your SAS SQL query into Oracle SQL.

Data never sleeps

View solution in original post


All Replies
Solution
‎05-26-2016 11:49 AM
Super User
Posts: 5,429

Re: SQL Pass-Thru vs Proc SQL - Same query has different results

Posted in reply to buechler66

It probably has to do with the use of NULL.

Try

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;

which will return how SAS/ACCESS libname will translate your SAS SQL query into Oracle SQL.

Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 184 views
  • 1 like
  • 2 in conversation