Desktop productivity for business analysts and programmers

Help with Oracle Passthrough a String Date thru Macro

Accepted Solution Solved
Reply
New Contributor RVP
New Contributor
Posts: 2
Accepted Solution

Help with Oracle Passthrough a String Date thru Macro

[ Edited ]

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. 


Accepted Solutions
Solution
‎06-06-2017 06:01 PM
New Contributor RVP
New Contributor
Posts: 2

Re: Help with Oracle Passthrough a String Date thru Macro

[ Edited ]

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. 

View solution in original post


All Replies
Solution
‎06-06-2017 06:01 PM
New Contributor RVP
New Contributor
Posts: 2

Re: Help with Oracle Passthrough a String Date thru Macro

[ Edited ]

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. 

☑ This topic is SOLVED.

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

Discussion stats
  • 1 reply
  • 103 views
  • 0 likes
  • 1 in conversation