topic SAS/SQL Error in SAS Procedures

SAS/SQL Error

pp2014 — Thu, 07 May 2015 17:32:44 GMT

I am using PROC Sql Pass-through facility in SAS by connecting to Oracle.

I am having the problem when I run the query on UNIX.

Query works fine on Windows platform My query has following lines in the where statement that causes the error:(Rec_date has a datetime format)

proc sql;

connect to oracle(user=xxx orapw="xyz" path="abc");

create table test_tbl as

select *

from connection to oracle (

select * from test

where trunc(x.rec_date) between trunc(nvl(a.eff_dt, '01-JAN-1900')) and trunc(nvl(b.end_dt, '31-DEC-9999')) );

quit;

ERROR: ORACLE execute error: ORA-01858: a non-numeric character was found where a numeric was expected.

I would appreciate any help in this..

Re: SAS/SQL Error

Hercules — Fri, 08 May 2015 02:46:30 GMT

The error is of Oracle with the error code 01858.

This error can be because the data types of your columns are not date.

Also I don't see any unit of measure with your truncate function as well. Are you trying to truncate date-time field to date?

Also, is it the same query you are trying to run?

Because I don’t see any alias name for the tables referred as 'a' , 'b' and 'x'. And if it is just an example and rec_date is date-time format then try nvl function with a date-time replacement, such as '31-dec-9999:00:00:00'.

It would be helpful if you can provide your requirement.

Re: SAS/SQL Error

jakarman — Fri, 08 May 2015 05:15:46 GMT

The nvl is an oracle function and you are getting an oracle error. Something is wrong with Oracle. Most likey the input data is not as expected with x.rec_date an/or b.end_dt.