Results 1 to 11 of 11

091703, 05:36 #1Registered User
 Join Date
 Sep 2003
 Posts
 19
Unanswered: How to use DECODE function oracle8i
Hi,
I've a doubt. please help me.
I'm migrating the database from sql server to oracle.
My problem is, I have to convert the following code which is inside the Procedure of sql server to oracle.
sql server

UPDATE table1
SET column1 = CASE WHEN column1> 2
THEN column1 2
ELSE column1
END
The same thing is working in the SQL prombt but not working in procedure.
As i come to know that PLSQL wonn't support 'CASE'.Decode is used for the function of 'case'
I want to this using the decode...
can anyone give me the idea how to use it.
thks advance..
arul

091703, 05:48 #2Moderator.
 Join Date
 Sep 2002
 Location
 UK
 Posts
 5,171
Provided Answers: 1Re: How to use DECODE function oracle8i
Oracle PL/SQL does support CASE, but only since 9i.
DECODE equivalent is:
DECODE (SIGN(column12),1,column12,column1)
which means: "if SIGN(column12) = 1 THEN return column12 ELSE return column1"
The SIGN function returns 1, 0 or 1 depending on whether its argument is negative, zero or positive.Tony Andrews
http://tinyurl.com/tonyandrews

091703, 06:24 #3Registered User
 Join Date
 Sep 2003
 Posts
 19
Re: How to use DECODE function oracle8i
Thank you. Its working.
Originally posted by andrewst
Oracle PL/SQL does support CASE, but only since 9i.
DECODE equivalent is:
DECODE (SIGN(column12),1,column12,column1)
which means: "if SIGN(column12) = 1 THEN return column12 ELSE return column1"
The SIGN function returns 1, 0 or 1 depending on whether its argument is negative, zero or positive.

091703, 06:37 #4Registered User
 Join Date
 Sep 2003
 Posts
 19
Re: How to use DECODE function oracle8i
Sorry for the trouble again.....
I have one more problem in that query. ie I want to check as a>5 instead of checking a=5.
Here is the Query i wanted to work in Oracle 8i.
for example:
UPDATE TABLE1 SET
COLUMN1= CASE WHEN COLUMN1>5
THEN 2 ELSE 3 END,
I tried
Update table1 set column1= decode(a,>5,2,3);
But its giving error on'>'.
Any solution please....
Thks advance.
Arul

091703, 06:39 #5Moderator.
 Join Date
 Sep 2002
 Location
 UK
 Posts
 5,171
Provided Answers: 1Re: How to use DECODE function oracle8i
That is exactly the same as before except using 5 instead of 2!
Use SIGN function as in my previous answer.Tony Andrews
http://tinyurl.com/tonyandrews

091703, 07:00 #6Registered User
 Join Date
 Sep 2003
 Posts
 19
Re: How to use DECODE function oracle8i
I am very sorry again.
Clearly what i want is, i want to chak wheather 'a' is greater then 5 ,if its greater assign a=2 else a=3.
for that i tried as
update table1 set a=decode(sign(a>5),1,2,3);
In terms of if,
if(a>5) then a=2 else a=3;
plz give me somthing handy
arul.

091703, 07:17 #7Moderator.
 Join Date
 Sep 2002
 Location
 UK
 Posts
 5,171
Provided Answers: 1Re: How to use DECODE function oracle8i
I thought I already did:
SIGN(column12)
Or for your new variation:
SIGN(a5)
"a minus 5", not "a greater than 5"
Suggest you reread my original answer carefully.Tony Andrews
http://tinyurl.com/tonyandrews

091703, 07:31 #8Registered User
 Join Date
 Sep 2003
 Posts
 19
Re: How to use DECODE function oracle8i
YES. Its working now.
Feel bad about my miscommunication.Sorry for the trouble i've given to u.
thks
Arul
Originally posted by andrewst
I thought I already did:
SIGN(column12)
Or for your new variation:
SIGN(a5)
"a minus 5", not "a greater than 5"
Suggest you reread my original answer carefully.

091803, 07:16 #9Registered User
 Join Date
 Sep 2003
 Posts
 19
Re: How to use DECODE function oracle8i
hi,
I have one more similler type of problem.
following query, which i want to convert from sqlserver to oracle.
UPDATE portal_roles
SET lid = CASE
WHEN lid BETWEEN @field1 AND @field2 THEN lid + @field1
ELSE lid END,
i tried in oracle
Update t set a= decode( sign(a3),1,a, ( sign(a10),1,a,8 ) );
Its giving error ( and ) 'outer' of the second SIGN.
Is there any way to solve the probs

091803, 07:51 #10Moderator.
 Join Date
 Sep 2002
 Location
 UK
 Posts
 5,171
Provided Answers: 1Re: How to use DECODE function oracle8i
You need another DECODE in there:
Update t set a= decode( sign(a3),1,a, decode( sign(a10),1,a,8 ) );
Don't think you have the logic right though: that says:
CASE
WHEN a>3 THEN a
WHEN a>10 THEN a
ELSE 8
END
I think you probably want:
CASE
WHEN a<3 THEN a
WHEN a>10 THEN a
ELSE 8
END
Which is:
Update t set a= decode( sign(a3),1,a, decode( sign(a10),1,a,8 ) );Tony Andrews
http://tinyurl.com/tonyandrews

091803, 09:17 #11Registered User
 Join Date
 Sep 2003
 Posts
 19
Re: How to use DECODE function oracle8i
Thank you.
Originally posted by andrewst
You need another DECODE in there:
Update t set a= decode( sign(a3),1,a, decode( sign(a10),1,a,8 ) );
Don't think you have the logic right though: that says:
CASE
WHEN a>3 THEN a
WHEN a>10 THEN a
ELSE 8
END
I think you probably want:
CASE
WHEN a<3 THEN a
WHEN a>10 THEN a
ELSE 8
END
Which is:
Update t set a= decode( sign(a3),1,a, decode( sign(a10),1,a,8 ) );