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

Reply
Highlighted
Green Belt

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

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? 


Accepted Solutions
Highlighted
Red Belt

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?



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post


All Replies
Highlighted
Red Belt

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?



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post

Highlighted
Green Belt

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

 

Thankyou. 

Highlighted
Black Belt

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.



**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Highlighted
Green Belt

It worked! ThankYou

Announcements
Domopalooza 2021 Call for Presenters: We want to hear how Domo is revolutionizing the way you do business!

Click here to submit your story.