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

or look at the docs

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')

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

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

Natural vs surrogate!

Nice points Mike

Thanks guys. As always, I appreciate the help and direction. COALESCE sounds like my best option.

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