Oracle Prepare Error
in [SAS]
|
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 13:26 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: Joe Matise on 30 Oct 2009 14:17 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: "Shafiroff, Jeff" on 30 Oct 2009 14:32 Thanks Joe,=20 =20 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.=20 =20 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.=20 =20 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 =20 Jeff ________________________________ From: Joe Matise [mailto:snoopy369(a)gmail.com]=20 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 =3D 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=20 On Fri, Oct 30, 2009 at 12:26 PM, Shafiroff, Jeff <Jeff.Shafiroff(a)blueshieldca.com> wrote: Thanks Gerhard, =09 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?) =09 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). =09 Jeff =09 =09 =09 -----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 =09 right, I see it. Was too long and I've forgotten the ( when I saw the )... =09 Another idea: because the statements are executed in ORACLE - could you test it in ORACLE without SAS to verify the syntax? =09 Gerhard =09 =09 =09 =09 On Fri, 30 Oct 2009 11:42:54 -0400, J Shafiroff <jeff.shafiroff(a)BLUESHIELDCA.COM> wrote: =09 >My error: should read ")" at 161 should go with "SELECT (" at line 113. > >Thank You =09
From: Joe Matise on 30 Oct 2009 14:41 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: "Shafiroff, Jeff" on 30 Oct 2009 15:14
Thanks Joe,=20 =20 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.=20 =20 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.=20 =20 It feels like the plumber selecting tools for the carpenter?=20 =20 =20 Jeff Shafiroff Medical Informatics Blue Shield of California=20 jeff.shafiroff(a)blueshieldca.com <mailto:jeff.shafiroff(a)blueshieldca.com>=20 6300 Canoga Avenue, 9th Floor Woodland Hills, CA 91367 Phone: 818.228.2663=20 Fax: 818.228.5494=20 =20 ________________________________ From: Joe Matise [mailto:snoopy369(a)gmail.com]=20 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...=20 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 =3D MBR_ID_DMNSN.UNIQ_MBR_ID) mid, DRUG_DMNSN dd, PRVDR_DMNSN pd WHERE (PHRMCY_CLM_FACT.FINL_CLM_KEY =3D PHRMCY_FINL_CLM_DMNSN.FINL_=20 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,=20 =20 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.=20 =20 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.=20 =20 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 =20 Jeff =09 ________________________________ From: Joe Matise [mailto:snoopy369(a)gmail.com]=20 Sent: Friday, October 30, 2009 11:18 AM To: Shafiroff, Jeff Cc: SAS-L(a)listserv.uga.edu=20 Subject: Re: Oracle Prepare Error =09 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. =09 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.) ... =09 Actually, look at this line: (biplr_up_v1.mbr_id =3D MBR_ID_DMNSN.UNIQ_MBR_ID) =09 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=20 =09 =09 On Fri, Oct 30, 2009 at 12:26 PM, Shafiroff, Jeff <Jeff.Shafiroff(a)blueshieldca.com> wrote: =09 Thanks Gerhard, =09 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?) =09 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). =09 Jeff =09 =09 =09 -----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 =09 right, I see it. Was too long and I've forgotten the ( when I saw the )... =09 Another idea: because the statements are executed in ORACLE - could you test it in ORACLE without SAS to verify the syntax? =09 Gerhard =09 =09 =09 =09 On Fri, 30 Oct 2009 11:42:54 -0400, J Shafiroff <jeff.shafiroff(a)BLUESHIELDCA.COM> wrote: =09 >My error: should read ")" at 161 should go with "SELECT (" at line 113. > >Thank You =09 |