Results 1 to 5 of 5

Thread: case statements

  1. #1
    Join Date
    Feb 2002
    Posts
    16

    Unanswered: case statements

    Hi guys
    I was wondering if you could help me.
    I have the following sql statement :

    select
    CUSTOMER,
    SERVICE,
    TELEPHONE,
    case
    when TELEPHONE='2'
    then "PVOIC"
    when TELEPHONE='3'
    then "MSISDN"
    when TELEPHONE='6'
    then "SDATA"
    when TELEPHONE='5'
    then "SFAX"
    when TELEPHONE='4'
    then "PPID"
    end
    from SERVICES

    It gives me the following results :

    2010 262 SDATA [NULL]
    2010 272 PVOIC [NULL]
    2010 280 SDATA [NULL]

    Is it possible not to have the [NULL] values ? Ideally, I would like :

    2010 262 SDATA
    2010 272 PVOIC
    2010 280 SDATA

    I tried removing the comma (,) after the TELEPHONE field of the sql but I get syntax errors.

    Any ideas ?
    I would be most grateful if you could let me know.

    Cheers
    Tony

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    Hiya Tho,

    You can use the ISNULL function.

    Cheers
    Willy

  3. #3
    Join Date
    Feb 2002
    Posts
    16

    case statements

    Hiya Wily
    Thanks for the email.
    I am still puzzled with your answer.
    You mentioned that ISNULL would serve my purposes. I am not sure how.

    From the previous email, I have the sql as follows :
    select
    CUSTOMER,
    SERVICE,
    TELEPHONE,
    case
    when TELEPHONE='2'
    then "PVOIC"
    when TELEPHONE='3'
    then "MSISDN"
    when TELEPHONE='6'
    then "SDATA"
    when TELEPHONE='5'
    then "SFAX"
    when TELEPHONE='4'
    then "PPID"
    end
    from SERVICES

    The result produces 4 values. The last value is always NULL based on the sql above.
    For example,

    customer service telephone
    ====== ===== ====== ======
    2010 262 SDATA [NULL]
    2010 272 PVOIC [NULL]
    2010 280 SDATA [NULL]

    The results are correct but I would like to hide away the values on the 4 column. So, I would like to end with the same results but without the 4 column values as follows :

    customer service telephone
    ====== ===== ======
    2010 262 SDATA
    2010 272 PVOIC
    2010 280 SDATA

    Any ideas ?
    I would be most grateful
    Thanks in advance
    Tony

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    put else '' in front of the end in the case statement

    the TELEPHONE column appears to contain values like SDATA, PVOIC, and SDATA, so i can't understand why you are testing them for 2,3,6,5,4

    perhaps what you meant to do is

    case
    when TELEPHONE='PVOIC'
    then '2'
    when TELEPHONE='MSISDN'
    then '3'
    when TELEPHONE='SDATA'
    then '6'
    when TELEPHONE='SFAX'
    then '5'
    when TELEPHONE='PPID'
    then '4'
    else ''
    end


    rudy

  5. #5
    Join Date
    Feb 2002
    Posts
    16

    case statements

    Thanks Rudy, Thanks Willy,

    I have just solved the problem.
    Thanks for the help.

    Tony

Posting Permissions

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