4 Replies - 1687 Views - Last Post: 25 October 2013 - 04:35 AM

#1 Dev_Tarun  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 28-September 13

sql error in stored procedure

Posted 24 October 2013 - 12:36 PM

Hi,
When I'm trying to create a stored procedure using this code

create or replace procedure CONCESSION_CALCULATE
(item_code IN varchar2, item_price IN numeric, con_price INOUT numeric)
AS
BEGIN
SELECT item.item_price
CASE item_price WHEN item_price<10 THEN item_price=item_price
WHEN item_price>=10 AND item_price<=100 THEN item_price=item_price-(item_price*0.1)
ELSE item_price=item_price-(item_price*0.2) END FROM item WHERE item.item_code=item_code


I'm getting following error.

"ERROR at line 8: PLS-00103: Encountered the symbol "WHERE" when expecting one of the following:

. ( ) * @ % & - + / at mod remainder rem
|| multiset
6. CASE item_price WHEN item_price<10 THEN item_price=item_price
7. WHEN item_price>=10 AND item_price<=100 THEN item_price=item_price-(item_price*0.1)
8. ELSE item_price=item_price-(item_price*0.2) END FROM item WHERE item.item_code=item_code"

Help please.

Is This A Good Question/Topic? 0
  • +

Replies To: sql error in stored procedure

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9583
  • View blog
  • Posts: 36,319
  • Joined: 12-June 08

Re: sql error in stored procedure

Posted 24 October 2013 - 12:45 PM

Don't you need a comma between line 5 and 6?
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3838
  • View blog
  • Posts: 13,595
  • Joined: 12-December 12

Re: sql error in stored procedure

Posted 24 October 2013 - 12:55 PM

I was going to say that BEGIN requires an END, but you do have this already. So, it is easier to read the statement if you split it across lines, and use indenting.
Was This Post Helpful? 0
  • +
  • -

#4 Dev_Tarun  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 28-September 13

Re: sql error in stored procedure

Posted 25 October 2013 - 02:15 AM

View Postmodi123_1, on 24 October 2013 - 12:45 PM, said:

Don't you need a comma between line 5 and 6?

Giving comma doesn't matter. It's again the same.
create or replace procedure CONCESSION_CALCULATE
(item_code IN varchar2, item_price IN numeric, con_price INOUT numeric)
AS
BEGIN
SELECT item.item_price,
CASE item_price WHEN item_price<10 THEN item_price=item_price
WHEN item_price>=10 AND item_price<=100 THEN item_price=item_price-(item_price*0.1)
ELSE item_price=item_price-(item_price*0.2) END FROM item WHERE item.item_code=item_code


It gives following error.

"ERROR at line 2: PLS-00103: Encountered the symbol "NUMERIC" when expecting one of the following:

:= . ) , @ % default character
The symbol ":=" was substituted for "NUMERIC" to continue.
1. create or replace procedure CONCESSION_CALCULATE
2. (item_code IN varchar2, item_price IN numeric, con_price INOUT numeric)
3. AS
4. BEGIN"

Can you please provide me a better tutorial for SQL stored procedures.
That would be really helpful.
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3838
  • View blog
  • Posts: 13,595
  • Joined: 12-December 12

Re: sql error in stored procedure

Posted 25 October 2013 - 04:35 AM

The first thing I would do would be to change your SP variable-names so that they are different to the field-names.

Your CASE statement is incorrect. the docs

CASE item_price 
WHEN item_price<10 THEN item_price 
WHEN item_price>=10 AND item_price<=100 THEN item_price-(item_price*0.1)
-- etc..

That is, you don't assign item_price a value, you indicate what value should be returned instead.

I'm not able to test this syntax currently, but I'm fairly confident ;)

You are also not assigning con_price a value, although I don't think that this is necessarily an error.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1