BookmarkSubscribeRSS Feed
pp2014
Fluorite | Level 6

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..

2 REPLIES 2
Hercules
Fluorite | Level 6

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.

jakarman
Barite | Level 11

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.

---->-- ja karman --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1051 views
  • 0 likes
  • 3 in conversation