Help with Oracle Passthrough a String Date thru Macro

RVP — Tue, 06 Jun 2017 21:52:12 GMT

Hello,

Sorry I am new to using SAS fulltime, however come with a solid SQL skillset.

Issue: I need to pass a date in the WHERE statement in an ORACLE Passthrough (Explicit) SQL Query. The date is a Macro field.

Below is the statement:

EXPRTN_DT > TO_DATE(&END_DATE,'DDMONYYYY')

In SAS, &END_DATE has a value of 31MAR2017. (Edit1: Without quotes and this macro is generated dynamically from SAS SQL using INTO: Function)

Steps tried other than the one mentioned above:

1. EXPRTN_DT > TO_DATE("&END_DATE",'DDMONYYYY')

2. EXPRTN_DT > TO_DATE('&END_DATE','DDMONYYYY')

3. EXPRTN_DT > TO_DATE('''&END_DATE''','DDMONYYYY') /*Note: Single Quotes surrounded by Single Quotes)*/

4. EXPRTN_DT > &END_DATE

5. EXPRTN_DT > "&END_DATE"

6. EXPRTN_DT > '&END_DATE'

7. EXPRTN_DT > '''&END_DATE''' /*Note: Single Quotes surrounded by Single Quotes)*/

If need be, willing to provide error messages, but most of them were as below:

1. ERROR: Error fetching from cursor. ORACLE error is ORA-12801: error signaled in parallel query server P019
ORA-01858: a non-numeric character was found where a numeric was expected.

2. Missing right Paranthesis.

Any help is greatly appreciated.

P.S. I was able to run the query in dBeaver with hardcoded date as '31MAR2017' and it worked fine.

Re: Help with Oracle Passthrough a String Date thru Macro

RVP — Tue, 11 Jul 2017 21:28:16 GMT

So wasted a day and oddly found the solution on Stackoverflow.

Solution:

Changed this: EXPRTN_DT > TO_DATE(&END_DATE,'DDMONYYYY')

To this: EXPRTN_DT > %BQUOTE('&QTR_END_DATE')

Credit: https://stackoverflow.com/questions/9830518/proper-syntax-for-sas-macro-date-in-oracle-query

Last Answer by Chris J.

topic Help with Oracle Passthrough a String Date thru Macro in SAS Enterprise Guide

Help with Oracle Passthrough a String Date thru Macro

Re: Help with Oracle Passthrough a String Date thru Macro