Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Unanswered: Is it possible to return a real value instead of a null value

    Using SQL, Is it possible to echo a real value like "Pending" if and when a field is null? I have 5 columns in my result set. 3 of these 5 will have null values for a time until the user updates the table. In my app code, I would have to test for null values and then write the proper code to echo something in its place until the value was available. Is it possible to do this with SQL? It would save me a small headache if this was possible.

    Thanks,
    Frank

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Code:
    select ifnull( status3, "Pending" )
    from MyTable
    or look at the docs

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i prefer to use COALESCE instead of IFNULL

    COALESCE is standard SQL, whereas IFNULL is proprietary to mysql

    in addition, COALESCE can take multiple parameters, so that, when necessary, you can write expressions like

    ... COALESCE(column1,column2,column3,'default')

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    In my app code, I would have to test for null values and then write the proper code to echo something in its place until the value was available. Is it possible to do this with SQL? It would save me a small headache if this was possible.
    The other option might be to just store the value "Pending" rather than storing NULL and later decoding it.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    The other option might be to just store the value "Pending" rather than storing NULL and later decoding it.
    that wouldn't work if it's a numeric column

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by r937
    that wouldn't work if it's a numeric column
    I often come across databases where each record has a status value of 1,2,3 and perhaps -2 and -5 are then added as error conditions. Then perhaps every year some new developer will add some new codes like 51 and 52 etc. Some of these new codes will invariably be almost identical to some existing forgotten codes. I personally dislike these integer codes for statuses but figure people put them in for various reasons:
    • Because it saves space - it might be noticeable in a db with billions of records but invariably, in most db's, it hardly makes any saving. The code on the other hand becomes bloated and unreadable - each sproc will be full of code like where status_id in ( 1,3,-5,0 ) which means nothing to man nor beast.
    • Because it goes faster - I suppose the indexes will be more compact with more more nodes on a given part of the b-tree but to be honest I doubt if you'd notice any difference in real life. Also if you have to join with another table to be able to decode your integer status values then you've probably lost any perceived gain.
    • Because they can add extra functionality to their codes - ie where status_id < 0. It might work while the original designer is still working there but it quickly degenerates into unreadable code. A better way might be to use flags within the status table.
    • Because it looks more like a proper database - this is the funniest reason to me but I suspect that's why most designers do it.
    There may be a few cases where integer status id's might be better but, for most of the databases I've come across, they'd of been far better making their data readable and the code clearer by avoiding using integer status values.

    Mike

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Natural vs surrogate!

    Nice points Mike
    George
    Home | Blog

  8. #8
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Thanks guys. As always, I appreciate the help and direction. COALESCE sounds like my best option.


  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Rudy, you be da man!! That COALESCE function is perrrfect. Using it will save me from coding copious amounts of application code in the future.

    Thank you!!!!
    Frank

Posting Permissions

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