Coalescing names from multiple tables but ignoring blanks.

Hello.

I've got an SQL query where I have a SELECT line like ...

CASE WHEN ISNULL(SL.CUUSER2, '') <> '' THEN SL.CUUSER2 ELSE '[BLANK]' END [S/L Sort],

Select allOpen in new window

The concept here is that a NULL and a '' are to be shown as '[BLANK]' (the literal text that is).

I now have a similar query, where, rather than a single table, the sort is coming from 6 different tables.

So, convention says ...

COALESCE
		(
		CASE SL_1.CUSORT WHEN '' THEN NULL END,
		CASE SL_2.CUSORT WHEN '' THEN NULL END,
		CASE SL_3.CUSORT WHEN '' THEN NULL END,
		CASE SL_4.CUSORT WHEN '' THEN NULL END,
		CASE SL_5.CUSORT WHEN '' THEN NULL END,
		CASE SL_6.CUSORT WHEN '' THEN NULL END,
		'[BLANK]'
		) [S/L Sort],

Select allOpen in new window

But that results in an error ...

None of the result expressions in a CASE specification can be NULL.

Select allOpen in new window

Any suggestions?

One I have is to replace the FROM <tablename> with FROM (SELECT case when ...) tablename. But the query is already 614 lines long and whilst I can certainly do this, is there a shortcut I'm missing?

OOI. The massive query aggregates data from 30 tables over 13 different databases on 2 different SQL servers.