mahjag
asked on
decode statement in oracle 8i
I want to use decode in PLSQL package where I need to add a condition inside an insert statement when a product is defined as a specific constant name (like MyProd)
Insert statement has lots of columns inserted but I wanted to add one condition for a field units
insert into temp_table
(a,units,b,.. )
(select 'a',decode(prod,'MyProd',u
I know I had the syntax wrong for decode but this is what I like to do,
First test if the prod name is MyProd, if it is then
units will be units + units *0.10
How could this be accomplished?
Insert statement has lots of columns inserted but I wanted to add one condition for a field units
insert into temp_table
(a,units,b,.. )
(select 'a',decode(prod,'MyProd',u
I know I had the syntax wrong for decode but this is what I like to do,
First test if the prod name is MyProd, if it is then
units will be units + units *0.10
How could this be accomplished?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
p.s.
to corrent myself, you can ofcourse change units + units*0.1 into units * 1.1, just to make things a bit easier...
to corrent myself, you can ofcourse change units + units*0.1 into units * 1.1, just to make things a bit easier...
ASKER
Thanks for the feeback, sorry was not specific in my question, I dont know oracle 8 supports case
I am taking the value of units from table but if the product is myprod then changing units to unit + units*0.1. I dont know how this will be equal to units *1.1 (are we assuming units = 1 always?)
I am taking the value of units from table but if the product is myprod then changing units to unit + units*0.1. I dont know how this will be equal to units *1.1 (are we assuming units = 1 always?)
mahjag,
Yes, 8i supports the case statement:
insert into temp_table
(a,units,b,.. )
(select 'a',case when prod = 'MyProd' then units + units *0.10 else units end,null,'b',..)
Good luck!
DrSQL
Yes, 8i supports the case statement:
insert into temp_table
(a,units,b,.. )
(select 'a',case when prod = 'MyProd' then units + units *0.10 else units end,null,'b',..)
Good luck!
DrSQL
basically mine and DrSQL's statements will do the same, but since Oracle allways advise using Case you should probably use his statement.
Now, about the 1.1
If you do units + units * 0.1 then it will first do units*0.1 and the add it to units.
Basically it's like: units * 1 + units * 0.1 = units * 1.1
Now, about the 1.1
If you do units + units * 0.1 then it will first do units*0.1 and the add it to units.
Basically it's like: units * 1 + units * 0.1 = units * 1.1
ASKER
I tried case and I am getting compilation errors
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
try puting the entire case sentence into ()
and if that doesn't work you should try the decode way.
and if that doesn't work you should try the decode way.
Now that I think about it, I'm pretty sure that in Oracle 8i the CASE sentence was only supported in SQL and not in PL\SQL.
Only in 9i the two compilers were united and PL\SQL supported everything SQL dis, like the CASE sentence.
So the DECODE should work for you while the CASE won't.
Only in 9i the two compilers were united and PL\SQL supported everything SQL dis, like the CASE sentence.
So the DECODE should work for you while the CASE won't.
mahjag,
If you're on 8.1.7, then it's not the case statement. Can you post the segment of code where you have the insert?
Good luck!
DrSQL
If you're on 8.1.7, then it's not the case statement. Can you post the segment of code where you have the insert?
Good luck!
DrSQL
As far as I know oracle introduced the CASE starting with Oracle 9i.
insert into temp_table
(a,units,b,.. )
(select 'a',decode(prod,'MyProd',u
The syntax should be:
insert into temp_table
(a,units,b,.. )
select 'a',decode(prod,'MyProd',t
from ...
where ....
Take out the select parentheses and close the decode ones.
Also you cannont use units = units + units *0.10 inside the decode.
to_char is needed because 'MyProd' is a string.
units + units *0.10 = units*1.1 - is just arithmetic.
Good luck,
Narcis
insert into temp_table
(a,units,b,.. )
(select 'a',decode(prod,'MyProd',u
The syntax should be:
insert into temp_table
(a,units,b,.. )
select 'a',decode(prod,'MyProd',t
from ...
where ....
Take out the select parentheses and close the decode ones.
Also you cannont use units = units + units *0.10 inside the decode.
to_char is needed because 'MyProd' is a string.
units + units *0.10 = units*1.1 - is just arithmetic.
Good luck,
Narcis
sorry, I was a bit wrong
insert into temp_table
(a,units,b,.. )
select 'a',decode(prod,'MyProd',u
from ...
where ....
Forget about to_char, and add null in decode.
However I'm not 100% sure about CASE in oracle 8.1.7.
Regards,
Narcis
insert into temp_table
(a,units,b,.. )
select 'a',decode(prod,'MyProd',u
from ...
where ....
Forget about to_char, and add null in decode.
However I'm not 100% sure about CASE in oracle 8.1.7.
Regards,
Narcis
You have to give
(select 'a',decode(prod,'MyProd',u
(select 'a',decode(prod,'MyProd',u
All,
I count the same decode statement posted THREE times. Let's try to keep the value of our postings up. And, I have an 8i (8.1.7) database and use the CASE statement extensively.
Good luck!
DrSQL
I count the same decode statement posted THREE times. Let's try to keep the value of our postings up. And, I have an 8i (8.1.7) database and use the CASE statement extensively.
Good luck!
DrSQL
Mahjag,
If case is supported in 8i, as DrSQL indicates, then there is no reason that it shouldn't compile, other than you have a syntax error somewhere. Please post the insert code that includes the case statement that wouldn't compile. I think we may find there is a simple cure.
If case is supported in 8i, as DrSQL indicates, then there is no reason that it shouldn't compile, other than you have a syntax error somewhere. Please post the insert code that includes the case statement that wouldn't compile. I think we may find there is a simple cure.
If so,
select a, ...
,case when prod = 'MyProd' then units * .10
else units
end
,...