[Resolved] Apply default value when NULL

Get Help
1

I’m trying to display a specific value (string) when a columns value is NULL, I couldn’t find any way of doing it so far (looked on github and discourse).

Basically, I have a User that may (or may not) belong to a School, I’m trying to set a default value for the School.name column when the User.school_id is null.

I tried using a segment but it doesn’t change the display value, I tried to use a “Display values -> custom mapping” but I don’t have any other choice than “Use original value” (and it wouldn’t work well either in my case since I want to display the original value except when NULL.

Any idea/workaround?
Thanks.

2

Sounds like you’re using the question-builder. If so perhaps a more experienced user than myself can chime in. There is, however, one workaround I can recommend, back up your question under another name just in case and show the sql (upper right there’s a button for that), and when the window appears hit the convert to sql button (it will overwrite the question, that’s why you back it up first). From there take the field of interest and do a case statement OR an isnull (or whatever the equivalent is for your default database that you originally used in setting up Metabase). For mssql it works like this:
(CASE WHEN YourField IS NULL THEN YourString ELSE YourField END) AS YourField
or the much more efficient
ISNULL(YourField,YourString) AS YourField

1 Like
3

Thank you! I’ll give it a try, and probably do that in the SQL View -Metabase is reading data from- itself and not in the query, which will allow to use the query builder and be consistent too.

4

For those not using MS SQL, you’ll probably need to use something other than ISNULL. Oracle uses COALESCE which is also another option in MS SQL. Difference being that you’re not limited to just one alternative as COALESCE takes n parameters and returns the first non-null. Easier than having to nest ISNULL.
Not sure there is a downside, but I do find that SQL using ISNULL is easier to interpret!

1 Like
5

Using PostGreSQL I had to rely on the following:

COALESCE("school"."name", 'None') AS "school_name"

Thanks!

1 Like