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',units = units + units *0.10,null,'b',..)

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?

mahjagAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Information Technology SpecialistCommented:
Does 8i support case?
If so,
select a, ...
    ,case when prod = 'MyProd' then units * .10
     else units
     end
,...
GiggsipCommented:
I'm not sure I quite understand you,
Where are you taking the value of units from?
is it some variable you have? if so then:

insert into temp_table
(a,units,b,.. )
(select 'a',decode(prod,'MyProd',units + (units*0.10),units),null,'b',..)

also, what do you want to do if it's not 'MyProd'? I figured you'll just want to enter units itself so that's what I did in here...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GiggsipCommented:
p.s.
to corrent myself, you can ofcourse change units + units*0.1 into units * 1.1, just to make things a bit easier...
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

mahjagAuthor Commented:
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?)
DrSQLCommented:
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
GiggsipCommented:
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
mahjagAuthor Commented:
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
GiggsipCommented:
try puting the entire case sentence into ()
and if that doesn't work you should try the decode way.
GiggsipCommented:
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.
DrSQLCommented:
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
NarcisFCommented:
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',units = units + units *0.10,null,'b',..)

The syntax should be:

insert into temp_table
(a,units,b,.. )
select 'a',decode(prod,'MyProd',to_char(units + units *0.10)),'b',..
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
NarcisFCommented:
sorry, I was a bit wrong

insert into temp_table
(a,units,b,.. )
select 'a',decode(prod,'MyProd',units + units *0.10, null),'b',..
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
sapnamCommented:
You have to give

(select 'a',decode(prod,'MyProd',units + units *0.10,null,'b',..)

DrSQLCommented:
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
awking00Information Technology SpecialistCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.