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 change value in select operation

Discussion in 'SQL PL/SQL' started by dipakyadav, Feb 3, 2009.

  1. dipakyadav

    dipakyadav Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    i was facing a problem while changing the feched values in the select query
    ex

    Code (Text):
    SELECT  emp.sal FROM emp WHERE emp.sal=null;
    but i want to display the sal value equal to 0 (zero) when it is null
    how can i make this plz help.
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    you need to use the NVL function

    DO this:
    Code (Text):
    SELECT  NVL(emp.sal,0) FROM emp;
    This will display 0 for your emp.sal when it is null :)
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    But you have to use IS operator instead of = operator.

    If we combine the both queries you will get ,

    Code (Text):

    SELECT  NVL(emp.sal,0) SAL  FROM emp WHERE emp.sal IS null;
     
    This will fetch only those records with Sal is null.
    ie, SAL column in in the above query will be zero.
     
  4. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Alternative way, :)
    Code (Text):

    SELECT  decode(sal,null,0) SAL  FROM emp WHERE sal IS null;
    Or use case.