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!

How to assing values for a variable in Decode.,./

Discussion in 'SQL PL/SQL' started by Vicky, Aug 25, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    The query below works like,

    select * from employees where emp_id like decode(&val, 'ALL', '%', &val)

    If I give 'ALL', means it'll SELECT ALL EMP_ID's.,

    But the requirement is, I want to select only (10,20,30)
    on giving 'ALL'.,

    How can I achieve this.,/!
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    I just love this forum! People constantly trying to do things in Oracle in ways I've never even thought about...

    ...so, Vicky...your ALL is not really ALL?

    You may have to build the query programmatically and then do a Execute Immediate on the statement.

    CJ
     
    Vicky likes this.
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,

    You can have a try with the following:

    Code (SQL):
    SELECT * FROM employees
    WHERE ((employee_id IN (10, 20, 30) AND to_char(&val)='ALL') OR
            (employee_id = &val AND to_char(&val) != 'ALL')
           )
    ;
     
    Vicky likes this.
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula


    If your table has only (10,20,30) records then...

    select * from employees where emp_id like decode(&val, 'ALL', '%', &val) and emp_id in (10,20,30);
     
    Vicky likes this.
  5. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    DECODE takes one base value, checks equality of that base value against one or more comparison values and returns one result.
    You therefore cannot use 'ALL' as a comparison value and return multiple results. However, you could flip your DECODE to the other side of the equation. This would allow you to use the employee IDs as the comparison values rather than 'ALL'. In the below example, I use hard coded values of 'ALL' and '103' rather than an ampersand, but the ampersand would work as well:

    Code (Text):
    SELECT employee_id, first_name, last_name
    FROM   hr.employees
    WHERE  DECODE(employee_id, 100, 'ALL', 101, 'ALL', 102, 'ALL', employee_id) = 'ALL';

    EMPLOYEE_ID FIRST_NAME      LAST_NAME  
    ----------- --------------- -----------
            100 Steven          King      
            101 Neena           Kochhar    
            102 Lex             De Haan    
           
    SELECT employee_id, first_name, last_name
    FROM   hr.employees
    WHERE  DECODE(employee_id, 100, 'ALL', 101, 'ALL', 102, 'ALL', employee_id) = '103';

    EMPLOYEE_ID FIRST_NAME      LAST_NAME  
    ----------- --------------- -----------
            103 Alexander       Hunold    
     
    Vicky likes this.