"select" is a workhorse, "update" is as routine as a pair of pants. "coalesce" is something special

[qwantz]

An Ode To Coalesce

Coalesce
I must confess
You're my favourite database function

Coalesce
With such finesse
You return the first non-null value in the argument list

Coalesce
You do impress
With the lovely name you've chosen

Coalesce
When I assess
All the other database functions you're always among my favourites

Comments

[ultrastefan]

At first I thought this was an ode to Coalesce the band. Alas not.

[scarfboys]

I suppose the third could be.

[nunzio]

i too made this mistake.
i was wicked stoked.

[_fluffy]

So what good is it in real use, anyway? I'd never seen it come up before today, when suddenly one of our customers said, "Oh yeah we might need to use coalesce in [SQL-esque database engine], will that be possible?" and I'm thinking, "Okay, what the hell do you need this for, and why did you wait until now to say you might want it?"

They're also begging for us to allow inline parameters instead of bound parameters, too. Screw the security concerns and massive bloat that would add to the expression parser!

[qwantz]

It's so useful! There's situations in which you might have one optional, more-detailed field, and a default field, so you could use coalesce(maybeitsthere, definitelythere). Or you could have an option field, so you could use coalesce(optionalfield, 'somedefaultvalue'). Or you could have a calculation which might return null, and using this you can trap that and stop it from spreading outword by subbing in 0!

It's invaluable in very specific situations. I was wishing for such a function and got partway into coding it before I discovered coalesce years ago, and it has been tru luv ever since.

[_fluffy]

Oh, I see, so like,

SELECT COALESCE(this,that,theother) FROM foo;

as opposed to SELECT this,that,theother FROM foo; and having the client have to do dumb fallback. But that doesn't help if the app also needs to know which column gave the data (which is the case for this app).

I can see how it might be useful in some contexts but I really can't see its value for our clients, so. I think it's more like they're asking for a complete SQL implementation "just in case they need it" when in the context of this system there's no reason they could even pretend to expect that (this is on an embedded system, to start with). Gah.

[thestormsurfer]

"invaluable", which does not mean the opposite of "valuable". What a language!

[_fluffy]

Also completely apropos to nothing but will you ever fix the OhNoRobot XML export to actually emit the separate panels? I've raised this both on the ONR forum and by email and you are usually good about replying to things but on this one thing you have been surprisingly quiet and for some reason it just crossed my mind to ask you again.

[qwantz]

I guess I haven't! Send me another email with the details and I'll try to get to it this month.

[_fluffy]

Not really much detail to give! Just that every comic gets all the panels' lines in a single
tag rather than each panel separated out like it's supposed to be, so there's no way for XML consumers to know where the panels begin and end.

I had emailed it to the ohnorobot contact email address instead of your qwantz one so maybe that is why you never saw it?

Anyway I will forward the message to your qwantz address (goodness, I sent it back in December).

[professor]

Coalesce is misspelled: Currently the title says "An Ode To Coalese."

[qwantz]

Thanks!

[professor]

Just doing my editorial duty.

[stegosaurus_rex]

Also "favourite" is less correctly spelled than it should be.

Love,
An American.

[boxer_the_horse]

Exactly. It should be spelled F-A-V-Oh! You ruined everyone's economy! Great Job!-R-I-T-E.

[stegosaurus_rex]

Ha!

[thestormsurfer]

No, it was me. I ruined everyone's economy.