Do MySQL dataflows support the use of the WITH clause?

I'm trying to use a WITH clause as outlined here:

https://dev.mysql.com/doc/refman/8.0/en/with.html

in one of my MySQL dataflows.

 

But I keep getting this error:

 

"The database reported a syntax error. You have an error in your SQL syntax"

 

My (very simple) code is:

 

WITH test AS (SELECT`EmaiName` FROM transform_data_2)
SELECT `EmaiName` FROM test

 

As far as I can tell that should be correct - is this just a matter of Domo not supporting WITH?

 

Not a big deal either way, but it would be nice to know.

Tagged:

Comments

  • RGranada
    RGranada Contributor

    Hi,

     

    I had the same issue some time ago. And could not find a solution or documentation on DOMO about this.

     

    My workaround was to use a transform for each Common Table Expression and then reference them in another transform or output.

     

    Technically it's not the same but it serves the needs.

     

    Hope this helps.

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • I ended up doing the same thing. Sounds like WITH probably just isn't available in Domo then.

     

    Thanks for your input

  • @ocrkm

     

    If @RGranada reply addressed your question please marked it as solved.

     

    Also you can suggest this as an enhancement in our Ideas Exhange.

     

    Thanks!

  • Would be nice to know what other standard mySQL features arent supported by DOMO.   the With statement is commonly used.  Do we need to request an enhancement for every feature in mySQL that DOMO doesnt support?  Perhaps some doc would be nice to tell the end user "mySQL is not the actual mySQL, but a limited set of its abilities.  DOMO currently does not support the following mySQL functions/features...."

     

    WITH is probably been around for many versions.

  • brycec
    brycec Contributor

    To those reading this thread years later: When I looked into this, it is because the version of MySQL that Domo is running does not support WITH. You can check the version by running this command: SELECT VERSION(). That command outputs 5.6.37-82.2-82.2, however WITH and Common Table Expressions weren't added to MySQL until version 8.0. Based on this other thread, https://dojo.domo.com/discussion/51065/mysql-version, it seems like Domo may update it eventually but so far no dice.

    Was this comment helpful? Click Agree or Like below.
    Did this comment solve your problem? Accept it as the solution!