Oracle Dates filter not working with a SAS to Oracle Passthrough

[MR_E]

 Good Morning SAS World,

I am absolutely baffled by this error, as i have written something previously using the Date filters and it seemed to work fine, but with the below code i am getting back dates before the quoted range.

The purpose of this code is to pull transactions back with automated dates for the previous day only - however, it pull back everything that matches on the name logic regardless of the date

I.e if today is

12MAY2017

i want everything from the 11th - which is what the macro dates provide , but the below code pull entries back from 2013 for example

I have also included a COMMENTED OUT TO_DATE - nrbquote date filter, is this any better, if so what is wrong with mine?

Much appreciated as always

/*GET PREVIOUS WORKING DAY, THIS METHOD USES THE FRIDAY IF YESTERDAY WAS A WEEKEND*/
/*COLLECT TODAYS DATE (END_DATE) ASWELL SO THAT IF IT IS A WEEKEND THE TXNS PULLED WILL BE FOR THE WHOLE WEEKEND*/
DATA _NULL_;
    PreviousBusinessday = intnx('weekday',today(),-1) ;
 END_DATE=intnx('Day', today(), 0,'beginning');

CALL SYMPUT ('PreviousBusinessday',""|| PUT(PreviousBusinessday,DATE9.));
CALL SYMPUT ('END_DATE',""|| PUT(END_DATE,DATE9.));

RUN;

PROC SQL;

/*ENTER PROD PATH USER AND PASSWORD DETAILS BELOW*/

CONNECT TO ORACLE (PATH="XXXX" USER=XXXX password="&XXXpw.");
CREATE TABLE US_txns AS select * from connection to oracle

(SELECT DISTINCT X.*
FROM DB_US.TXNS X
WHERE
/*SEARCH FOR NAMES */
UPPER(TRIM(X.ORIG_NM)) LIKE '%ABC LIMITED%' OR
UPPER(TRIM(X.BENEF_NM)) LIKE '%ABC LIMITED%'

/* %str(%') required when using macro in Oracle as it does not accept double quotes. All strings and dates need to be wrapped in single quotes. */
AND X.TRXN_EXCTN_DT >= %str(%')&PreviousBusinessday.%str(%')
AND X.TRXN_EXCTN_DT < %str(%')&END_DATE.%str(%')

/*>= TO_DATE(%nrbquote(')&PreviousBusinessday%nrbquote('), 'YYYYMMDD')*/

);
DISCONNECT FROM ORACLE;
QUIT;

[SASKiwi · Accepted solution]

Maybe your ANDs and OR are getting confused. I would try this:

WHERE
/*SEARCH FOR NAMES */
(UPPER(TRIM(X.ORIG_NM)) LIKE '%ABC LIMITED%' OR
UPPER(TRIM(X.BENEF_NM)) LIKE '%ABC LIMITED%')
/* %str(%') required when using macro in Oracle as it does not accept double quotes. All strings and dates need to be wrapped in single quotes. */
AND X.TRXN_EXCTN_DT >= %str(%')&PreviousBusinessday.%str(%')
AND X.TRXN_EXCTN_DT < %str(%')&END_DATE.%str(%')
/*>= TO_DATE(%nrbquote(')&PreviousBusinessday%nrbquote('), 'YYYYMMDD')*/

[MR_E]

Hi SAS Kiwi,

typically, as i posted the issue i noticed the absence of the brackets!

Fixed it straight away and its fine!

Thanks for the response anyway

[Kurt_Bremser]

I guess that Oracle does not like the SAS date literals. Most RDBMS I know want date literals as "YYYY-MM-DD".