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

buechler66 — Thu, 26 May 2016 14:28:38 GMT

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;

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

LinusH — Thu, 26 May 2016 15:11:55 GMT

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.

topic Re: SQL Pass-Thru vs Proc SQL - Same query has different results in SAS Programming

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

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