05-07-2015 01:32 PM
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 errorRec_date has a datetime format)
connect to oracle(user=xxx orapw="xyz" path="abc");
create table test_tbl as
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')) );
ERROR: ORACLE execute error: ORA-01858: a non-numeric character was found where a numeric was expected.
I would appreciate any help in this..
05-07-2015 10:46 PM
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.
05-08-2015 01:15 AM
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.