LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (October 2009, week 5)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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
Comments: To: Carol Bristow <>
In-Reply-To:  <>
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 6300 Canoga Avenue, 9th Floor Woodland Hills, CA 91367 Phone: 818.228.2663 Fax: 818.228.5494

-----Original Message----- From: Carol Bristow [] 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 <> 6300 Canoga Avenue, 9th Floor Woodland Hills, CA 91367 Phone: 818.228.2663 Fax: 818.228.5494


From: Joe Matise [] Sent: Friday, October 30, 2009 11:42 AM To: Shafiroff, Jeff Cc: 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...


On Fri, Oct 30, 2009 at 1:32 PM, Shafiroff, Jeff <> 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



From: Joe Matise [] Sent: Friday, October 30, 2009 11:18 AM To: Shafiroff, Jeff Cc:

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 <> 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).


-----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?


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

Back to: Top of message | Previous page | Main SAS-L page