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

[Dreamer]

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

[SASKiwi]

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;

[Reeza]

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.

[Dreamer]

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.

[SASKiwi]

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.

[Patrick]

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.

[LinusH]

Your SQL generates a full table scan, so pushing the case to Oracle should not gain much performance.

[Dreamer]

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.

[DimaSup]

Errors:

SAS_SQL: Failed to get a DBMS-specific datetime value. 163681 1621320885 no_name 0 SQL (469
SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error. 163682

Timestamp 18.05.2021 09:54:45
Page Faults 0
Page Reclaims 136
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

MLOGIC(MEQUIBEHSCOREUPRE): %PUT mvNow= &mvNow
SYMBOLGEN: Macro variable MVNOW resolves to 1936950885.51
mvNow= 1936950885.51
MPRINT(MEQUIBEHSCOREUPRE): proc sql;
SYMBOLGEN: Macro variable T_CLIENT_ID resolves to 00000031-B081-4E97-9437-F20CF874F857
SYMBOLGEN: Macro variable MVNOW resolves to 1936950885.51
MPRINT(MEQUIBEHSCOREUPRE): select COALESCE(sum(abc.step_sum),0) into :SumLoans_12m from RTDM_ABT.ABT_CONTRACT abc where abc.CLIENT_ID = "00000031-B081-4E97-9437-F20CF874F857" and 1936950885.51
- abc.CONTRACT_BEGINDATE <= (365*24*60*60) and abc.DML_FLAG NE 1 group by abc.CLIENT_ID;
163677 1621320885 no_name 0 SQL (469
SQLSRV_37146: Prepared: on connection 0 163678 1621320885 no_name 0 SQL (469
SELECT * FROM rtdm_abt . ABT_CONTRACT 163679 1621320885 no_name 0 SQL (469
163680 1621320885 no_name 0 SQL (469
SAS_SQL: Failed to get a DBMS-specific datetime value. 163681 1621320885 no_name 0 SQL (469
SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error. 163682 1621320885 no_name 0 SQL (469
ACCESS ENGINE: SQL statement was not passed to the DBMS, SAS will do the processing. 163683 1621320885 no_name 0 SQL (469
163684 1621320885 no_name 0 SQL (469
SQLSRV_37147: Prepared: on connection 0 163685 1621320885 no_name 0 SQL (469
SELECT STEP_SUM , CLIENT_ID , CONTRACT_BEGINDATE , DML_FLAG FROM rtdm_abt . ABT_CONTRACT WHERE ( ( CLIENT_ID = '00000031-B081-4E97-9437-F20CF874F857' ) AND ( DML_FLAG <> 1 ) )