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!

Else part in decode is not working.,/

Discussion in 'SQL PL/SQL' started by Vicky, Sep 9, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    When i fire this query below.,


    SELECT * FROM EMPLOYEES
    order by decode(&flag, 's', emp_id, emp_name) desc


    It's not working, when i give values other then 's'.,.


    While I fire the query below.,

    SELECT * FROM EMPLOYEES
    order by decode(&flag, 's', emp_id, 'r', emp_name, salary) desc

    Else part is working, but when I give 'r' it's showing the error below.,


    ORA-01722: invalid number
    01722. 00000 - "invalid number"
    *Cause:
    *Action:

    Could U tel me wat's the prblm with the queries.,/!
     
  2. rajenb

    rajenb Forum Expert

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

    The data types of the different options of the DECODE statement must be same.
    In your case, emp_id is a NUMBER (I suppose) and emp_name is a VARCHAR, that's why it's complaining that emp_name is an invalid number.

    It would work with, for example:

    Code (SQL):
    SELECT * FROM EMPLOYEES
    ORDER BY decode(&flag, 's', to_char(emp_id), emp_name) DESC
    In your second query, salary is also numeric, so it would work for "ORDER BY salary" ...
     
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Thanks Rajen.,

    But, if I use to_char it's not working properly.,.

    > select * from employees order by emp_id desc;

    SNO EMP_ID EMP_NAME SALARY DOB MANAGER_ID CITY
    --- ------ -------------------------------------------------- ------ --------- ---------- ----------
    15 80 Ram 5000 21-JAN-96 100 Delhi
    10 50 elisa 9000 23-JAN-99 60 Delhi
    9 40 Dev 8000 30-MAR-92 10 Chennai
    8 30 celin 5000 02-FEB-99 50 Delhi
    7 20 bala 5000 01-JAN-91 20 Chennai
    15 10 Ram 6595 19-JUN-14 40 Chennai
    15 8 McCullam 50000 21-MAR-96 90 Mumbai
    12 7 Dev 5231 21-MAR-96 80 Chennai

    8 rows selected

    > SELECT * FROM EMPLOYEES
    ORDER BY decode(&flag, 's', to_char(emp_id), emp_name) DESC;

    when I give 's'.,


    SNO EMP_ID EMP_NAME SALARY DOB MANAGER_ID CITY
    --- ------ -------------------------------------------------- ------ --------- ---------- ----------
    15 80 Ram 5000 21-JAN-96 100 Delhi
    15 8 McCullam 50000 21-MAR-96 90 Mumbai
    12 7 Dev 5231 21-MAR-96 80 Chennai
    10 50 elisa 9000 23-JAN-99 60 Delhi
    9 40 Dev 8000 30-MAR-92 10 Chennai
    8 30 celin 5000 02-FEB-99 50 Delhi
    7 20 bala 5000 01-JAN-91 20 Chennai
    15 10 Ram 6595 19-JUN-14 40 Chennai

    8 rows selected
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Sure, the output will differ - I did the to_char just to avoid getting the oracle error.

    If you want same output, then use LPAD(...) :

    Code (SQL):
    SELECT * FROM EMPLOYEES
    ORDER BY decode(&flag, 's', lpad(to_char(emp_id), 15), emp_name) DESC;
    Note: I've used LPAD(.., 15), assuming 15 to be the max size of empno; you can increase it if you wish.
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    you compare different types of sortings...
    What sorting order is necessary for you?
     
  6. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Tnx guys., It's wrkng now,.,.:)