Oracle pass-thru query gives "ORA-00933: SQL command not properly

Oracle pass-thru query gives "ORA-00933: SQL command not properly

Post by pardee. » Wed, 20 Dec 2006 06:07:41


Hey All,

I've got some sas code that hits an oracle db (version 9 I think) that's
running dog-slow w/libname access. I'm trying to see if I can speed it
up w/pass-through, but am having trouble getting the syntax right it
seems.

Can somebody tell me what's wrong w/the following please?

proc sql ;
connect to oracle as ora (path = my_database user = roy password =
my_password) ;
create table gnu as
select * from connection to ora
(select drgkey
, source
, consumno
, admitdt
, dischdt
from drgsum as d
where d.AdmitDt between to_date('01-jan-2003') AND
to_date('31-dec-2003') AND
d.obflag = '2'
)
;
quit ;

That gives me the subject error. At first I thought I needed a
semicolon on the end of the passed-through statement, but if I do that I
get a 'ERROR 79-322: Expecting a ).' And I *can* do a 'select * from
dual' w/out the inner semicolon, so I think that bit is okay.

Can anybody throw me a clue here?

Thanks!

-Roy
 
 
 

Oracle pass-thru query gives "ORA-00933: SQL command not properly

Post by saikumarko » Wed, 20 Dec 2006 18:06:19

Hi Roy,

i am not an expert but i hav some idea behind this

if u are using v9 then i think its better to create dynamic library and
use the sas usual code ..it may be cheaper in terms of execution time
and

if u are still wanted to connect using code like proc sql

the condition with where clause is seems to be complex for the
compiler.

before using the where condition u can create one index on that varible
and then good to use where condition

let me know u r ideas

cheers,
sai

 
 
 

Oracle pass-thru query gives "ORA-00933: SQL command not properly

Post by xav » Thu, 21 Dec 2006 00:37:01

Hi,

Try :

proc sql ;
connect to oracle as ora (path = my_database user = roy password =
my_password) ;
create table gnu as
select * from connection to ora
(select drgkey
, source
, consumno
, admitdt
, dischdt
from drgsum as d
where d.AdmitDt between to_date('01-01-2003', 'DD-MM-YYYY') AND
to_date('31-12-2003', 'DD-MM-YYYY') AND
d.obflag = '2'
)
;
quit ;

Xavier


XXXX@XXXXX.COM a rit :
 
 
 

Oracle pass-thru query gives "ORA-00933: SQL command not properly

Post by A. Michie » Mon, 22 Jan 2007 13:38:37

ssuming you are in the US - with NLS set, you'll probably
find the following works fine too.
You may discover that with the Oracle TO_DATE function,
that an index on the date less likely to be used.

proc sql ;
connect to oracle as ora (path = my_database user = ...
create table gnu as
select * from connection to ora
(select drgkey
, source
, consumno
, admitdt
, dischdt
from drgsum as d
where d.AdmitDt between '01/01/2003' AND '12/31/2003'
AND d.obflag = '2'
);
quit ;



"xav" < XXXX@XXXXX.COM > wrote in
news: XXXX@XXXXX.COM :