proc sql to oracle specific date

[ivs]

hi all,

can someone please help!

I can't figure out how to pull data for a specific date.

I am trying to query oracle database.

proc sql noerrorstop;

connect to oracle (user='XX' password='XX' buffsize=100 path='XX');

create table work.CUST as

select DATA_DT, EMPL_ID, CUST_NBR, Count(CUST_NBR)

from connection to oracle (select * from MYTABLE)

where DATA_DT = dmy(01, 01, 2013) and EMPL_ID in (select * from work.empls);

group by 1, 2, 3, 4;

disconnect from oracle;

quit;

i get error for the above:

ERROR: The original SQL statement contains a UDF that is not being passed down to the database.

       SQL execution is stopped.

ERROR: Expression using equals (=) has components that are of different data types.

many thanks in advance!

[stat_sas]

Try this.

proc sql noerrorstop;

connect to oracle (user='XX' password='XX' buffsize=100 path='XX');

create table work.CUST as

select DATA_DT, EMPL_ID, CUST_NBR, Count(CUST_NBR)

from connection to oracle (select * from MYTABLE)

where DATA_DT = '01JAN2013'd and EMPL_ID in (select EMPL_ID from work.empls)

group by 1, 2, 3, 4;

disconnect from oracle;

quit;

[ivs]

to stat@sas:

this format gets no error messages, but the query hangs up with no results.

to LinusH:
thanks! gonna try it.

[LinusH · Accepted solution]

Don't use explicit pass-thru.

In your case, SAS handles the date conversion for you AND pushes part of the where clause to the RDBMS if you use implicit pass-thru (that is, query a Oracle libname instead).

[DBailey]

You're only selecting three variables to group by but your grouping by 4.  Not sure if that's by design.

One other issue with oracle is that I believe SAS considers most oracle date columns (at least all of ours) to be datetime, not date...so you would have to use '01Jan2013:0:0'dt.

[ivs]

to LinusH: The libname approach worked! Thank you!

to DBailey: its just a minor typo there - thanks for pointing this out. LinusH's answer resolved the fundamental problem.