Home » SQL & PL/SQL » SQL & PL/SQL » Problems with ElseIf (Oracle 9.2.0.4)
Problems with ElseIf [message #418150] Thu, 13 August 2009 03:34 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi there,

I have an IF statement in my code which is failing the compiler.
I am on Oracle 9.2.0.4


if r_dist.dst_gl_dist_revclass IS NULL then
v_matchCount:= v_matchcount;
elseif r_wip.dsti_revenue_class = r_dist.dst_gl_dist_revclass then
v_matchCount:= v_matchcount+1;
else v_misMatch := 1;
end if;


the compiler returns:

Error report:
ORA-06550: line 37, column 8:
PLS-00103: Encountered the symbol "R_WIP" when expecting one of the following:

   := . ( @ % ;
The symbol ":=" was substituted for "R_WIP" to continue.
ORA-06550: line 37, column 43:
PLS-00103: Encountered the symbol "THEN" when expecting one of the following:

   . ( * @ % & - + ; / at mod rem <an exponent (**)> and or ||

Am I doing something fundamentally wrong here?

thanks in advance,
Matt
Re: Problems with ElseIf [message #418152 is a reply to message #418150] Thu, 13 August 2009 03:43 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
ELSIF not ELSEIF
Re: Problems with ElseIf [message #418154 is a reply to message #418150] Thu, 13 August 2009 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A good reading:
PL/SQL User's Guide and Reference

Regards
Michel
Re: Problems with ElseIf [message #418155 is a reply to message #418152] Thu, 13 August 2009 03:52 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

d'oh.
cannot believe I didnt spot that!!

thanks!
Re: Problems with ElseIf [message #418156 is a reply to message #418150] Thu, 13 August 2009 03:55 Go to previous messageGo to next message
shaz
Messages: 182
Registered: June 2009
Senior Member
seems you are a newbie, read the content given by Michel.
however i will tell the solution:

Quote:
if r_dist.dst_gl_dist_revclass IS NULL then
v_matchCount:= v_matchcount;
elseif r_wip.dsti_revenue_class = r_dist.dst_gl_dist_revclass then
v_matchCount:= v_matchcount+1;
else v_misMatch := 1;
end if;


Dear how the compiler will come to know that you are using a database field--
you have to write the fields like
:r_dist.dst_gl_dist,
:r_wip.dsti_revenue.... etc.

[Updated on: Thu, 13 August 2009 03:57]

Report message to a moderator

Re: Problems with ElseIf [message #418159 is a reply to message #418156] Thu, 13 August 2009 04:01 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Wrongggg! Read the post again, read through Michels link and read my post. Wink
Re: Problems with ElseIf [message #418160 is a reply to message #418156] Thu, 13 August 2009 04:05 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
shaz wrote on Thu, 13 August 2009 09:55
seems you are a newbie, read the content given by Michel.
however i will tell the solution:

Quote:
if r_dist.dst_gl_dist_revclass IS NULL then
v_matchCount:= v_matchcount;
elseif r_wip.dsti_revenue_class = r_dist.dst_gl_dist_revclass then
v_matchCount:= v_matchcount+1;
else v_misMatch := 1;
end if;


Dear how the compiler will come to know that you are using a database field--
you have to write the fields like
:r_dist.dst_gl_dist,
:r_wip.dsti_revenue.... etc.



Unless this is oracle forms :r_dist.dst_gl_dist won't compile.
I'm assuming it's a PL/SQL record in which case the stated syntax is correct.

However

@matpj
v_matchCount:= v_matchcount;


You do realise that unlike some other languages PL/SQL doesn't have case sensitive variables?
That line just sets the variable to itself.
Re: Problems with ElseIf [message #418161 is a reply to message #418159] Thu, 13 August 2009 04:08 Go to previous messageGo to next message
shaz
Messages: 182
Registered: June 2009
Senior Member
Yap, offcourse elsif is correct.
apart from that I thought i was in forms forum.
Thumbs Up
Re: Problems with ElseIf [message #418163 is a reply to message #418161] Thu, 13 August 2009 04:10 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
apart from that I thought i was in forms forum.
Bit of a newbie mistake there eh? Wink
Re: Problems with ElseIf [message #418164 is a reply to message #418159] Thu, 13 August 2009 04:11 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

are you saying wrong to me?!

I changed elseif to elsif and it seems to work now Smile
I'm purposefully setting the variable to itself there, because in my many attempts to get the code working I thought I would need to put an action of some kind in the first 'IS NULL' statement!

I can remove that bit now that pablolee has shown me the error of my elseif!!

thanks all,
Matt
Re: Problems with ElseIf [message #418167 is a reply to message #418150] Thu, 13 August 2009 04:18 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think the wrong was aimed at shaz.

If you want to put placeholders while writing PL/SQL I suggest you use NULL.
Re: Problems with ElseIf [message #418168 is a reply to message #418164] Thu, 13 August 2009 04:19 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
matpj wrote on Thu, 13 August 2009 10:11
are you saying wrong to me?!

Nope. You can tell who I am speaking to by looking at the post header where it states (and hyperlinks to) the message that is being replied to. (Alternatively, view in threaded mode)
Re: Problems with ElseIf [message #418170 is a reply to message #418164] Thu, 13 August 2009 04:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you want to include the first IF but don't want it to do anything, you can rewrite it as this:
 if r_dist.dst_gl_dist_revclass IS NULL then
  null;
elsif r_wip.dsti_revenue_class = r_dist.dst_gl_dist_revclass then
  v_matchCount:= v_matchcount+1;
else
   v_misMatch := 1;
end if;


[Correct ELSIF which I copied from the wrong post Embarassed ]

[Updated on: Thu, 13 August 2009 04:21]

Report message to a moderator

Re: Problems with ElseIf [message #418181 is a reply to message #418170] Thu, 13 August 2009 05:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
JRowbottom wrote on Thu, 13 August 2009 11:21
Correct ELSIF which I copied from the wrong post Embarassed ]

LOL

Watch out, or people will start calling you a newbie too..

[thinking]Never actually got it why this is considered such a big thing. I met colleagues that were new to the business, that I could work with WAAAAAAY better than some of the old farts..[/thinking]

[Updated on: Thu, 13 August 2009 05:06]

Report message to a moderator

Re: Problems with ElseIf [message #418184 is a reply to message #418181] Thu, 13 August 2009 05:17 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

I thought i had started a fight for a moment there!

thank you all for your input.
Yep, I am a newbie to PL/SQL - but thanks to the assistance I am getting from people on this forum i'm making good headway.

i've also got a copy of Oracle PL/SQL For Dummies open in front of me as I work.
it is written in plain black and white text 'elsif', but in my mind I was saying else if as I typed it...

I wont be making that mistake again!

Regards,
Matt
Re: Problems with ElseIf [message #418185 is a reply to message #418184] Thu, 13 August 2009 05:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Next time you encounter an error, try not to focus too much on the actual location Oracle tells you the error occurs.
Often, the actual culprit for errors lie somewhat before the reported location. (This only goes for compilation errors!!)
Oracle loses context at the actual error-location and encounters something it does not expect at the reported location.
Re: Problems with ElseIf [message #418186 is a reply to message #418184] Thu, 13 August 2009 05:29 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
matpj wrote on Thu, 13 August 2009 11:17

I wont be making that mistake again!


Don't bank on it mate. I still make that mistake myself. Sometimes, stuff like that just needs a second pair of eyes. You often tend to read what you think you have typed rather than what you've actually typed Very Happy
Previous Topic: view to be split into multiple views
Next Topic: using UTL_FILE for bulk_exceptions
Goto Forum:
  


Current Time: Sun Dec 11 07:50:36 CST 2016

Total time taken to generate the page: 0.08978 seconds