Unsupported SQL type '{columnType}' for column 'VARBINARY'

hamza_123

I want to assign NULL values to certain columns in a SQL transform:

 

SELECT

NULL as 'column_x',

NULL as 'column_y',

column_z

FROM Table_1

 

The dataflow gives the following error: Unsupported SQL type '{columnType}' for column 'VARBINARY' 

 

This is because from what I understood from another post in Dojo: you cannot assign NULL values in MySQL to a text column. However, we're currently doing that in another SQL transform in another dataflow!! Why does it run in that dataflow but not in this dataflow I fail to udnerstand! We assign NULL values to a text column in that dataflow

 

I did a workaround where I started to assign blank values instead of NULL.

SELECT

'' as 'column_x',

'' as 'column_y',

column_z

FROM Table_1

 

It worked! However I do a COALESCE function in the next transform, such as the following:

COALESCE(column_x, column_y, column_z)

 

Now because column_x is non-null, it starts to assign column_x values to above. However, It actually should've been null (I only assigned blank to column_x and column_y as a workaround). The true result of above should be column_z

 

Could someone help me here please? 

GrantSmith

Hi @hamza_123 

I believe Domo has the ability you to utilize the NULLIF() function where it will return if a field meets a specific column.

 

 

COALESCE(NULLIF(column_x, ''), NULLIF(column_y, ''), NULLIF(column_z, ''))

 

 

 

If not you could wrap it in CASE statements.

 

CASE WHEN `column_x` <> '' THEN
  `column_x`
WHEN `column_y` <> '' THEN
  `column_y`
ELSE
  `column_z`
END

 

 

 

As for your original question, NULLs should be allowed under a string column. Are you only returning NULL or is it mixed with other string values?

GrantSmith

Hi @hamza_123 

I believe Domo has the ability you to utilize the NULLIF() function where it will return if a field meets a specific column.

 

 

COALESCE(NULLIF(column_x, ''), NULLIF(column_y, ''), NULLIF(column_z, ''))

 

 

 

If not you could wrap it in CASE statements.

 

CASE WHEN `column_x` <> '' THEN
  `column_x`
WHEN `column_y` <> '' THEN
  `column_y`
ELSE
  `column_z`
END

 

 

 

As for your original question, NULLs should be allowed under a string column. Are you only returning NULL or is it mixed with other string values?

hamza_123

The NULLIFF is a very smart way to look at things. Let me try that and get back to you here!

 

Thankyou. 

MarkSnodgrass

Spoiler

The following did not give me an error when trying it in Domo's MySQL :

SELECT CAST(NULL as char) as mycol

You could try that and see if that works for you.

hamza_123

It worked! ThankYou