Prev: DDE to read Excel file with autofilter turned on?
Next: why simple kappa are different in proc freq order=data, and order=internal
From: "Shafiroff, Jeff" on 30 Oct 2009 18:45 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 31 Oct 2009 11:53 - 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 2 Nov 2009 16:14
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: |