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? 

Best Answer

  • GrantSmith
    GrantSmith Indiana 🔴
    Accepted Answer

    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?

Answers

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

     

    Thankyou. 

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤
    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.
  • It worked! ThankYou