Date:         Mon, 18 Dec 2006 13:46:29 -0800
Reply-To:     "Pardee, Roy" <pardee.r@GHC.ORG>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Pardee, Roy" <pardee.r@GHC.ORG>
Subject:      Re: Oracle pass-thru query gives "ORA-00933: SQL command not
              properly ended"
Comments: To: "Kamireddi, Srinivas" <ksriniva@OXHP.COM>
Content-Type: text/plain; charset="US-ASCII"

Ah--there it is!  Many thanks!

So let me try everyone's patience w/a follow-up question--for this bit
here:

  where AdmitDt between to_date('01-jan-2003') AND
                        to_date('31-dec-2003')

My admit dates have time portions specified on them.  If I understand
this right, I will not actually get admits happening after midnight on
31-dec-2003, which I want.  This is apparently acceptable to oracle:

  where AdmitDt between to_date('01-jan-2003') AND
                       (to_date('31-dec-2003') + .999)

Does that do what I want?  Is there a better way?

Thanks!

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Kamireddi, Srinivas
Sent: Monday, December 18, 2006 1:22 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Oracle pass-thru query gives "ORA-00933: SQL command not
properly ended"

Prefix the schema name like

Pardee.drgsum

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Pardee, Roy
Sent: Monday, December 18, 2006 4:19 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Oracle pass-thru query gives "ORA-00933: SQL command not
properly ended"

Ah--promising!

Now I'm getting a "ORA-00942: table or view does not exist.", which
seems odd (this is adapted from code that works & that table should
definitely be there) but I think I can probably debug that myself...

Thanks!

-Roy

-----Original Message-----
From: toby dunn [mailto:tobydunn@hotmail.com]
Sent: Monday, December 18, 2006 1:11 PM
To: Pardee, Roy; SAS-L@LISTSERV.UGA.EDU
Subject: RE: Oracle pass-thru query gives "ORA-00933: SQL command not
properly ended"

Roy ,

Take your 'as' out of 'from drgsum as d'  It should be 'from drgsum d'
Orale doesnt like 'as'.





Toby Dunn

To sensible men, every day is a day of reckoning. ~John W. Gardner

The important thing is this: To be able at any moment to sacrifice that
which we are for what we could become. ~Charles DuBois

Don't get your knickers in a knot. Nothing is solved and it just makes
you walk funny. ~Kathryn Carpenter






From: "Pardee, Roy" <pardee.r@GHC.ORG>
Reply-To: "Pardee, Roy" <pardee.r@GHC.ORG>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Oracle pass-thru query gives "ORA-00933: SQL command not
properly
            ended"
Date: Mon, 18 Dec 2006 13:07:41 -0800

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

_________________________________________________________________
Fixing up the home? Live Search can help
http://imagine-windowslive.com/search/kits/default.aspx?kit=improve&loca
le=en-US&source=hmemailtaglinenov06&FORM=WLMTAG