Where can we find the actual error which is causing the query to fail. The brief information provided in the transformation is not enough. Most of the times it simply says syntax error.
Thanks for your question, we will need a lot more information to diagnose the issue.
Can you share a screen shot of the error you are getting?
I am asking in general, all the time we get those syntax errors the msgs are very brief.
Domo will generally just run the query and hand back any errors that Redshift says that you have. It's not generally a "user facing" version of the error. It's just the straight up error that it ran into. Unfortunately, sometimes Redshift doesn't give much information when a query fails due to syntax. If there is an error that you feel was misleading, you can raise a ticket on support.domo.com and they can take a look to see if it's something they can pretty up. You can also let them know on the errors where you want more detail, but it's possible that there is no additional detail for them to give. Either way, they should be able to help clear it up.
The only surefire way to figure out the error is to break apart the query and figure out which bit is failing. You can check out the documentation for Redshift syntax here: https://docs.aws.amazon.com/redshift/latest/dg/c_SQL_commands.html