Results 1 to 11 of 11
  1. #1
    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How to use DECODE function oracle8i

    Oracle PL/SQL does support CASE, but only since 9i.

    DECODE equivalent is:

    DECODE (SIGN(column1-2),1,column1-2,column1)

    which means: "if SIGN(column1-2) = 1 THEN return column1-2 ELSE return column1"

    The SIGN function returns -1, 0 or 1 depending on whether its argument is negative, zero or positive.

  3. #3
    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(column1-2),1,column1-2,column1)

    which means: "if SIGN(column1-2) = 1 THEN return column1-2 ELSE return column1"

    The SIGN function returns -1, 0 or 1 depending on whether its argument is negative, zero or positive.

  4. #4
    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

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: 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.

  6. #6
    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.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How to use DECODE function oracle8i

    I thought I already did:

    SIGN(column1-2)

    Or for your new variation:

    SIGN(a-5)

    "a minus 5", not "a greater than 5"

    Suggest you re-read my original answer carefully.

  8. #8
    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(column1-2)

    Or for your new variation:

    SIGN(a-5)

    "a minus 5", not "a greater than 5"

    Suggest you re-read my original answer carefully.

  9. #9
    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(a-3),1,a, ( sign(a-10),1,a,8 ) );
    Its giving error ( and ) '-outer-' of the second SIGN.

    Is there any way to solve the probs

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How to use DECODE function oracle8i

    You need another DECODE in there:

    Update t set a= decode( sign(a-3),1,a, decode( sign(a-10),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(a-3),-1,a, decode( sign(a-10),1,a,8 ) );

  11. #11
    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(a-3),1,a, decode( sign(a-10),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(a-3),-1,a, decode( sign(a-10),1,a,8 ) );

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •