From: "Shafiroff, Jeff" on
Thank you Carol. I will give that a try. Always, there does seem to be
format incompatibilities between SAS and ORACLE. And I can never get
the same method to work more than once. It is very frustrating.

Someone on an Oracle ListServe - at least the version I sent there,
pointed out I had "between" twice. Well, corrected that - and now I get
a new error:

"Invalid Identifier: ORACLE prepare error: ORA-00904" at another field -
"PHRMCY_CLM_FACT"."LAST_SRVC_DT". However, both the table and field are
entered correctly, per documentation.

I do see in the log, the passing of from and to dates as macros from
SAS, echoing back as correct dates, but they are in ddmonyyyy format
(e.g. 01JAN2009). I swear this format worked correctly just last week
for another query to the same data mart, via similar SAS pass through.

The source of all this effort to accomplish something so simple is
mind-boggling. I cannot see why anyone would want to store data in
Oracle by choice. We have suggested, finding a DBA to run Oracle side
data extractions from given programming specifications, but to no avail.




Jeff Shafiroff
Medical Informatics
Blue Shield of California
jeff.shafiroff(a)blueshieldca.com
6300 Canoga Avenue, 9th Floor
Woodland Hills, CA 91367
Phone: 818.228.2663
Fax: 818.228.5494


-----Original Message-----
From: Carol Bristow [mailto:Carol.Bristow(a)dpra.com]
Sent: Friday, October 30, 2009 3:29 PM
To: Shafiroff, Jeff
Subject: RE: Re: Oracle Prepare Error

I think I recall from the original query that part of your logic is
looking for what appears to be a date field to be between two dates that
appear to have been passed through as character stringss. In some cases
Oracle will automatically do the conversion to a datetime variable so
that it can process the query, but perhaps in this case it isn't
happening. I already purged the original query, so I forget exactly how
your dates were formatted. But, I would try changing that part of the
query to

Datefield between to_date('01012009','mmddyyyy') and
to_date('02022009','mmddyyyy')

If I've remembered the format of the date strings incorrectly, you can
change the order of the mask. Mm is month, dd is date, and yyyy is the
year.

Carol Bristow
DPRA Inc.
1655 N. Ft Myer Dr. Ste925
Arlington, VA 22209
phone: 703-682-2612
fax: 703-528-3100


-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of
Shafiroff, Jeff
Sent: Friday, October 30, 2009 3:14 PM
To: SAS-L(a)LISTSERV.UGA.EDU
Subject: Re: Oracle Prepare Error

Thanks Joe,

Yes, I see the table is missing in the FROM clause. Just added it
directly, but same error. Verified all field names and table names.

However, I will examine closely your suggestion here. Agreed, on
decisions in business. In my own area of expertise, I feel qualified
to select tools, but cannot do so in this setting.

It feels like the plumber selecting tools for the carpenter?



Jeff Shafiroff
Medical Informatics
Blue Shield of California
jeff.shafiroff(a)blueshieldca.com
<mailto:jeff.shafiroff(a)blueshieldca.com>
6300 Canoga Avenue, 9th Floor
Woodland Hills, CA 91367
Phone: 818.228.2663
Fax: 818.228.5494




________________________________

From: Joe Matise [mailto:snoopy369(a)gmail.com]
Sent: Friday, October 30, 2009 11:42 AM
To: Shafiroff, Jeff
Cc: SAS-L(a)listserv.uga.edu
Subject: Re: Oracle Prepare Error


The issue if I am reading your code right is that you don't refer to it
in the FROM portion of the query...
I would probably put it in a subquery that returned MID, though I
imagine Oracle would do that for you anyway in its optimization :

FROM
PHRMCY_CLM_FACT pcf,
(select m.* from MBR_ID_DMNSN m, biplr_up_V1 where
biplr_up_v1.mbr_id = MBR_ID_DMNSN.UNIQ_MBR_ID) mid,
DRUG_DMNSN dd,
PRVDR_DMNSN pd
WHERE
(PHRMCY_CLM_FACT.FINL_CLM_KEY =
PHRMCY_FINL_CLM_DMNSN.FINL_
CLM_KEY) AND (... etc - I only removed the first WHERE clause)

Also, can't believe someone would give you access to an oracle server
but not a desktop query tool. I'd think you would be far more likely to
screw something up submitting via SAS pass through than in a properly
limited account in TOAD... and there are free tools available (both a
free TOAD older version, if I recall correctly, and Oracle's own tools).
But employers rarely make decisions based on logic, unfortunately...


-Joe


On Fri, Oct 30, 2009 at 1:32 PM, Shafiroff, Jeff
<Jeff.Shafiroff(a)blueshieldca.com> wrote:


Thanks Joe,

No desktop tools bought/available/allowed here. Yes, at first
when I ran into the pass-through code concept, > 1 yea ago, I figured
out the difference between the SAS and Oracle side, and clearly this is
an Oracle side error.

Yes, biplr_up_v1 is a table that was uploaded (successfully). I
did notice the table alias/reference issue you suggest. I gave it an
explicit alias in an earlier query version, but still had the same
error. I only need the biplr_up_v1.mrb_id for matching, so and I might
then rename the MBR_ID_DMNSN.UNIQ_MBR_ID - as mbr_id to output it, in
the table/temporary sas work file. The final issue: will also (re)check
is field and table name spellings.

It is a rather complex manner (if not "optimized") to simply
extract data. I wish for the days where we had properly trained/staffed
DB management professionals to assist on this - they liked it, and were
very good at it!. I will also circle back to look for an Oracle
ListServ

Jeff

________________________________

From: Joe Matise [mailto:snoopy369(a)gmail.com]
Sent: Friday, October 30, 2009 11:18 AM
To: Shafiroff, Jeff
Cc: SAS-L(a)listserv.uga.edu

Subject: Re: Oracle Prepare Error


Do you have any sort of Oracle desktop tool (Oracle's own tool,
or TOAD, or something similar)? If so you should be able to just
execute the inside query directly in it, and get a better idea of what
the problem is. The issue here is that Oracle's giving an error, and
then passing to SAS that there was an error, but the error isn't
usefully communicated beyond that.

My guess would be it's something like a typing error (a date
instead of a char variable, a number instead of a char, etc.) ...

Actually, look at this line:
(biplr_up_v1.mbr_id = MBR_ID_DMNSN.UNIQ_MBR_ID)

What is biplr_up_v1 referring to? It looks to be a table (hence
the statement after the big one) but it's not referenced in that first
set of statements.
-Joe


On Fri, Oct 30, 2009 at 12:26 PM, Shafiroff, Jeff
<Jeff.Shafiroff(a)blueshieldca.com> wrote:


Thanks Gerhard,

That would be a great idea. Unfortunately I have no
formal background
in Oracle PL/SQL so not clear how to do so. Within SAS,
using PROC SQL,
I would expect this would be easier to address (so why
is Oracle so
popular?)

Someone had raised the notion of something called
"Explain Plan"
specifically for Oracle "PL-SQL"; Apparently, if one
could get through
the Oracle syntax parser, it can report how the SQL
Query Execution is
actually taking place. It issues
recommendations/warnings (e.g. to avoid
Cartesian joins).

Jeff



-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU]
On Behalf Of
Gerhard Hellriegel
Sent: Friday, October 30, 2009 8:55 AM
To: SAS-L(a)LISTSERV.UGA.EDU
Subject: Re: Oracle Prepare Error

right, I see it. Was too long and I've forgotten the (
when I saw the
)...

Another idea: because the statements are executed in
ORACLE - could you
test it in ORACLE without SAS to verify the syntax?

Gerhard




On Fri, 30 Oct 2009 11:42:54 -0400, J Shafiroff
<jeff.shafiroff(a)BLUESHIELDCA.COM> wrote:

>My error: should read ")" at 161 should go with
"SELECT (" at line
113.
>
>Thank You

From: Tom Abernathy on
- Check the value of the macro variables. For example is this snippet
generating a valid BETWEEN clause?
(PHRMCY_CLM_FACT.LAST_SRVC_DT BETWEEN &q.&fdos.&q and &q.&ldos.&q)
- Check your variable names. Are any of them valid Oracle operators?
I know I have had trouble with SAS version of SQL when I wanted to use
a variable name that looked like a function or command name to SQL.
What I have seen people do routinely with MYSQL is enclose all of
their field (variable) and table (dataset) references in backquotes so
that the syntax parser knows they are not commands.

- Tom Abernathy



On Oct 29, 6:09 pm, jeff.shafir...(a)BLUESHIELDCA.COM (J Shafiroff)
wrote:
> Hello I am using pass-through code to access Oracle with SQL. I do not
> seem to be able to get around this prepare error. Can someone please
> help?  Thank you. SAS Log file appears below:
From: "Shafiroff, Jeff" on
Thank You Tom. These are points well taken. Beginning to think the
"pass-through" feature ay be rather unpredictable and unstable, at least
in our environment - or there is some small gotcha in the query itself
that no one and certainly not I can see.

Also posted to Oracle List Serve, and sent to SAS tech support who
recommend running query directly in Oracle. Must wait for some
tools/info at our site to do so.

Recall an older way to connect to RDBMs from SAS - with the SAS CONNECT
product. It brought back views into the data, and one could use SAS
PROC SQL directly as I recall - avoiding learning another ANSI SQL.
Seemed more reliable, if not more efficient - but I would prefer to take
whatever works.


Jeff Shafiroff



-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of Tom
Abernathy
Sent: Saturday, October 31, 2009 8:53 AM
To: SAS-L(a)LISTSERV.UGA.EDU
Subject: Re: Oracle Prepare Error

- Check the value of the macro variables. For example is this snippet
generating a valid BETWEEN clause?
(PHRMCY_CLM_FACT.LAST_SRVC_DT BETWEEN &q.&fdos.&q and &q.&ldos.&q)
- Check your variable names. Are any of them valid Oracle operators?
I know I have had trouble with SAS version of SQL when I wanted to use a
variable name that looked like a function or command name to SQL.
What I have seen people do routinely with MYSQL is enclose all of their
field (variable) and table (dataset) references in backquotes so that
the syntax parser knows they are not commands.

- Tom Abernathy



On Oct 29, 6:09 pm, jeff.shafir...(a)BLUESHIELDCA.COM (J Shafiroff)
wrote:
> Hello I am using pass-through code to access Oracle with SQL. I do not

> seem to be able to get around this prepare error. Can someone please
> help? Thank you. SAS Log file appears below: