1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle 9i SQL DECODE Function Limitation

Discussion in 'SQL PL/SQL' started by ygsunilkumar, May 8, 2009.

  1. ygsunilkumar

    ygsunilkumar Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Limited to 255 comparisons in CASE statement. Each WHEN … THEN pair is considered 2 comparisons. To avoid exceeding the limit of 128 choices, you can nest CASE expressions. That is return_expr can itself be a CASE expression

    The maximum number of components that you can have in a decode function is 255. This includes the expression, search, and result arguments.

    In the above two statements, for CASE Statements there is solution that CASE can be nested to avoid exceeding the limit of 128 choices.

    Is there any solution for DECODE Function to avoid exceeding the limit of 255? Thanks in advance
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    well i think you can use decode statements within decode, that is nest them. Some more knowledgable members can give examples perhaps
     
  3. ygsunilkumar

    ygsunilkumar Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Sadik i have posted under this...Oracle Forums > Queries and Discussions > Oracle Database > SQL PL/SQL

    is that worng, please let me know. Thanks
     
  4. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    no it's absolutely right
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
  6. ygsunilkumar

    ygsunilkumar Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    @Rajavu, Is there any solution for DECODE Function to avoid exceeding the limit of 255? Thanks in advance
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Did u check this section of the link ?

    Code (SQL):

    SELECT somecolumn,
           CASE WHEN somecolumn LIKE 'FRA/0001%' THEN  
                CASE WHEN somecolumn LIKE 'FRA/00011%' THEN 'its a 11'  
                     WHEN somecolumn LIKE 'FRA/00012%' THEN 'its a 12'
                     WHEN somecolumn LIKE 'FRA/00013%' THEN 'its a 13'
                     WHEN somecolumn LIKE 'FRA/00014%' THEN 'its a 14'
                     WHEN somecolumn LIKE 'FRA/00015%' THEN 'its a 15'
                     WHEN somecolumn LIKE 'FRA/00016%' THEN 'its a 16'
                     WHEN somecolumn LIKE 'FRA/00017%' THEN 'its a 17'
                     WHEN somecolumn LIKE 'FRA/00018%' THEN 'its a 18'
                     WHEN somecolumn LIKE 'FRA/00019%' THEN 'its a 19'
                END
           END
    FROM sometable
    WHERE somecolumn LIKE 'FRA/0001%'