Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

SQL Commands in Alteryx

gurney1138
5 - Atom

Hello Alteryx users,

 

Are there solutions to put comments in the SQL Editor of the "Connect In-DB" Box ?

 

Also, sometimes, Alteryx rewrite the code in the SQL Editor (it adds the full path "library.table" to the variable name) making the code less readable. How avoid it ?

 

Thanks beforehand.

 

Franck Berthuit

7 REPLIES 7
AndréB
Alteryx Alumni (Retired)

For me the sql standard for beginning a comment and ending a comment syntax  works just fine. For example

 

/*insert your comment between the asterisks*/

 

Editing the way the query writes the full path isn't possible. But you can rename selected columns by typing inside of the "Alias" option within the bottom pane of the visual query builder when individual columns are selected.

 

Hopefully that helps.

 

-AndréB

gurney1138
5 - Atom

Thank you Andre for your answer... but no, unfortunately, the /* my comment */ syntax does'nt work !!!

AndréB
Alteryx Alumni (Retired)

I attached a screenshot of how i've commented on a basic query without any errors. I did find a couple of things while testing this out.

 

  • One finding is that the comments does get removed if you have the query builder change your query. Is that what is happening for you?
  • Another finding is that the if the comment is inserted before the query itself it errors out. Not sure why but I see that happening on my end and was wondering if you were experincing the same. if so try placing it after your query and see if that works.

-AndréB

RodL
Alteryx Alumni (Retired)

As @AndréB mentions, the Visual Query Builder tab can "reconfigure" you SQL code, so I wouldn't rely on having your code retain formatting or comments inside the tool.

If you are wanting to have the code documented with comments, etc. in a more readable format within a workflow so others can reference it, a suggestion would be to copy the code you have written with all of the documentation and format that you want, and paste it into a Comment tool and place right below the Connect In-DB tool.

This way it is always in the form you want with the comments.

gurney1138
5 - Atom

Hi André,

 

- yes; i noticed that the comments and format change when i switch from the SQL Editor to the Visual Query Builder and back (Cf. Rod message). A manoeuvre to avoid :-)

 

- i have made several test with comments in the code : sometimes it works, even when the comments is inside the code, not just at the end, and sometimes it fails. To unstable for me. I'll take Rod's suggestion : write the code with comments in a comment box, and paste and copy in the Connect In-DB box with some cleaning of the comments in case of error message !

 

Thank you for the time you spend to answer.

 

Franck 

mst3k
11 - Bolide

it would be really helpful to fix this.. how often do you want your comments at the end of your SQL instead of the beginning? nobody will ever know they're there.

i understand putting the comments in a comment tool in the alteryx workflow instead - but this isn't visible to anybody else. SQL DBAs can see the code you're running, and it's helpful to have comments at the top of your code to trace ownership, purpose, etc which the DBA would be able to see. if it's just in your alteryx workflow, they can't see any of that

jordanmount6
7 - Meteor

It would also be helpful for Analytical App Workflows. 

I have a query in a dynamic input where I have lines in the where clause commented out. The user can choose from a checklist what they want to query for and then update the query code with a text box value. The action tool has an 'update value with formula' and i have the formula uncommenting the line if the user check marks the box. I cannot do this since it wont work with commented lines. The only way the app i made will work in this format is if the user check marks all the boxes, uncommenting all of the sql lines. 

Labels