SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error

Reply
Frequent Contributor
Posts: 98

SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error

I am writing a proc sql using SAS/ACCESS for Oracle.

This is my library statement:

libname oradb oracle user=&sysuserid. password='{SAS002}XXXX' path=db_path schema=db_schema sql_functions=all;

 

I used sastrace to check the generated sql and execution location of this sql:

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

 

 

Usually generated sql is executing in Oracle environment.

But when I use PUT() to change forrmat then sql is executing in SAS environment and its impacting performance:

SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error.

ACCESS ENGINE: SQL statement was not passed to the DBMS, SAS will do the processing.

 

I have a requirement to change the datetime and number to character format before using it in case when statement:

 

proc sql;

 select

  case

    when a.id is not null then put(a.age,8.)

    when a.id is null then 'N/A'

    as age

 from oradb.table_nm as a;

quit;

 

Is there other way to change datetime or Number to Char so that SQL can process in DBMS environment?

Any help or suggestion wil be much appreciate. Thanks

Super User
Posts: 3,254

Re: SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error

Using SQL passthru will give you more flexibility (untested):

 

proc sql;
connect to oracle (user=&sysuserid. password='{SAS002}XXXX' path=db_path schema=db_schema);
 select
  case
    when missing(id) then 'N/A'
    else put(age, 8.)
  end 
  as age2
  from connection to oracle
(select id,
age
from table_nm )
;
quit;
Super User
Posts: 19,810

Re: SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error

PUT isn't a valid SQL function. Use a SQL function, it sounds like ORACLE so googling Oracle convert number to character, then you can use TO_CHAR

 

 

TO_CHAR(age)

 

 

This is assuming you're using direct SQL pass through and not implicit. 

 

The list of functions that can be passed directly to the server is here:

http://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#p0f64yzzxbsg8un1uw...

 

You haven't really explained this:

I have a requirement to change the datetime and number to character format before using it in case when statement

 

 Depending on exactly what you're trying to do there may be other ways to accomplish your end goal.

Frequent Contributor
Posts: 98

Re: SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error

Thanks @SASKiwi for suggestion.

But here I can't use explicit passthrough because:

  • Since I dont have write access to Oracle server, I have created some views in SAS library pointing to Oracle schema.
  • I am using these libraries in my SQLs.

SAS/Access for Oracle takes care of generating DBMS specific SQL and passing it to Oracle server for processing.

 

Only time it fails when I use PUT() function.

Super User
Posts: 3,254

Re: SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error

You don't need write access to Oracle to run a passthru query that is only reading from an Oracle table. The advantage of passthru is that you can use your PUT function in the SAS side of the query.

Respected Advisor
Posts: 4,173

Re: SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error

I would actually have expected that for the code you've posted the Access engine would send the following for execution to the database:  select id from <oracle schema>.table_nm

 

And then only on the SAS side the case statement would get executed.

 

What @SASKiwi proposes is the way I would do it as well.

 

You could also try to re-formulate your query as follows. May be this way the Access engine gets it.

proc sql;
 select
  case
    when a.id is not null then put(a.age,8.)
    when a.id is null then 'N/A'
    as age
 from 
  (
    select id from oradb.table_nm
  )
 ;
quit;

 

It's another case if you want to apply SAS functions which can't get pushed to the data base in logical expressions as there the data would get transferred to SAS before reducing the volume. In such a case you want to write the code in native Oracle SQL as pass-through SQL.

Super User
Posts: 5,429

Re: SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error

Your SQL generates a full table scan, so pushing the case to Oracle should not gain much performance.
Data never sleeps
Frequent Contributor
Posts: 98

Re: SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error

Thanks everyone for suggestions.

Here I can't use explicit passthrough because:

  • Since I dont have write access to Oracle server, I have created some views in SAS library pointing to Oracle schema.
  • I am using these libraries in my SQLs.

SAS/Access for Oracle takes care of generating DBMS specific SQL and passing it to Oracle server for processing.

 

Only time it fails when I use PUT() function.

Ask a Question
Discussion stats
  • 7 replies
  • 860 views
  • 0 likes
  • 5 in conversation