Date: Fri, 30 Oct 2009 15:45:16 -0700
Reply-To: "Shafiroff, Jeff" <Jeff.Shafiroff@BLUESHIELDCA.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Shafiroff, Jeff" <Jeff.Shafiroff@BLUESHIELDCA.COM>
Subject: Re: Oracle Prepare Error
In-Reply-To: <84987185DAEBF64685F7946AAD3B0EDB9784FF@mhk-ex1.ad.dpra.com>
Content-Type: text/plain; charset="us-ascii"
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@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@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@LISTSERV.UGA.EDU] On Behalf Of
Shafiroff, Jeff
Sent: Friday, October 30, 2009 3:14 PM
To: SAS-L@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@blueshieldca.com
<mailto:jeff.shafiroff@blueshieldca.com>
6300 Canoga Avenue, 9th Floor
Woodland Hills, CA 91367
Phone: 818.228.2663
Fax: 818.228.5494
________________________________
From: Joe Matise [mailto:snoopy369@gmail.com]
Sent: Friday, October 30, 2009 11:42 AM
To: Shafiroff, Jeff
Cc: SAS-L@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@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@gmail.com]
Sent: Friday, October 30, 2009 11:18 AM
To: Shafiroff, Jeff
Cc: SAS-L@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@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@LISTSERV.UGA.EDU]
On Behalf Of
Gerhard Hellriegel
Sent: Friday, October 30, 2009 8:55 AM
To: SAS-L@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@BLUESHIELDCA.COM> wrote:
>My error: should read ")" at 161 should go with
"SELECT (" at line
113.
>
>Thank You
