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

hamza_123
hamza_123 Contributor
in Magic ETL

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 Coach
    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?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    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?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • hamza_123
    hamza_123 Contributor

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

     

    Thankyou. 

  • MarkSnodgrass
    MarkSnodgrass Coach
    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.
    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • hamza_123
    hamza_123 Contributor

    It worked! ThankYou

Categories