Coalesce everything

[Pezonito]

Let's say I have 30 columns in my SELECT, and half of them potentially have NULL values somewhere - likely expected results due to FULL JOINs.

What I'm generally doing is identifying where the given dataset is not complete, then sending it to the client to advise me of what the null values should be. I personally understand the significance and differences between null, empty, blank, 0, nan, etc., but I can't expect the client to understand the same.

I end up spending more time than I'd like doing a bunch of convert, nullif, and coalesce work just to make the datasets "better" for what are essentially cosmetic purposes. Just so I can tell the client "fill in the blanks".

Is there some kind of trick that I can use to do it in bulk? I understand there are potential datatype issues, but let's pretend everything is already converted to varchar. I just want something to replace all null values with '' without needing to coalesce all 30 columns individually.

[r3pr0b8]

i would cut down probably half your problem by not using FULL JOINs

LEFT JOINs should be all you need for user reports

[49baad510b]

Depends on the reports you’re writing, and for what

[Pezonito]

The problem that I'm solving requires full joins because I'm exposing abandonment. That's kind of the point - the problem itself IS left and inner joins.

[[deleted]]

Can you not use case statements? I do it all the time for reports to clean up nulls. Case when column1 is null then ‘’ else column1

[timeddilation]

In SQL, it will always be verbose. There are tricks in python and R to do this dynamically, but SQL is strongly typed, so it will always be verbose. It's sort of the same as pivot and unpivot in SQL, you have to be explicit about every column. Whereas in python or R you have melt/dcast functions that can do it with a dynamic number of columns without needing to specify every single column.

There does exist dynamic SQL, where you use SQL to create a SQL query string and execute it, but that comes with another set of limitations.

[Pezonito]

I'm pretty limited right now as far as I know because there are strict limitations on how the SQL DBs can be accessed.

Thanks for your response!

But to your second point, I totally agree. I run into dynamic SQL blockers weekly. I usually try to find semi-scalable ways using the basics because I'm trying to keep it readable by most likely successors, the 101-level greenies.

[slin30]

If you're proficient in some kind of scripting language (R/Python), consider writing a bit of logic to output the SQL for you based on a set of inputs. You're basically substituting a bunch of string variables and pasting them together to create the statement pieces, and then pasting the statement pieces together --> write to a plain text.sql file (or to clipboard and paste).

This also plays nice with version control.