Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!
The Product Idea boards have gotten an update to better integrate them within our Product team's idea cycle! However this update does have a few unique behaviors, if you have any questions about them check out our FAQ.

Alteryx Designer Desktop Ideas

Share your Designer Desktop product ideas - we're listening!
Submitting an Idea?

Be sure to review our Idea Submission Guidelines for more information!

Submission Guidelines

Have an SQL Tool

I constantly find my using pre and post SQL Commands in the Output tool to run SQL when I don't actually have any data to output.

 

One example is when I load data into S3 and want to load it into Redshift. I have SQL code to run but no data to Output - I end up running a dummy row into a temp table.

 

So can we have an SQL tool that simply acts the same as a Pre-SQL command without the associated data output. Once the command is run we should be able to continue the workflow, so the tool should have an option input and output, like the Run Command tool.

 

 

32 Comments
Atabarezz
13 - Pulsar

I'm sure you are aware there happens to be new tools "in the block" that try to combine

 

SQL, Python, R

 

And Alteryx is able to blend all three in a single workflow

and is much easier to use

and is much easier to learn

 

so would love to see a tool that works much like jupyter notebook so that I can write an SQL code quickly test if it works etc.

 

 

DSC-Studio-041218

 

 

Atabarezz
13 - Pulsar

default-theme

 

 

Also please take a look at TeamSQL, a great tool, an IDE (integrated dev ops environ) for SQL... 

This ability will ease the burden of migrating towards Alteryx from legacy platforms don't you think?

simonaubert_bd
13 - Pulsar

Ooooh.

I didn't find that when I post my idea.

As of today, you can pass SQL from :
-input tool

-output tool

-connect in-db

The user interface is very limited, the kind of query you can pass also, welll, not very user-friendly. This generates a lot of frustration among users.

What do I suggest :
1/ A direct button "Query Builder" without having to open a new wf and drop an input box and then go with the presql tool and fight to build a query.

2/ Basically the same features than Dbeaver (https://dbeaver.io ) or DBvisualizer (https://www.dbvis.com/) or SQUIRREL http://squirrel-sql.sourceforge.net/ ):
  -Ability to pass any SQL Code I want (such as update, create, truncate, etc...) when I come from the button, "protected" sql when I am in a workflow
  -autocompletion

  -color coding (The idea is not new )

 
3/ A box "Free Sql Query" that I can branch on a indb or standard wf to pass any SQL query. The Output would be the same as input, just like it would be with a block until done.
SrucBI
7 - Meteor

Upvote -

Also would like to see much more flexible ability to input SQL Commands without having to stick to a rigid "expectation" of what a SQL command looks like. 

 

eg let's say I want to pull in the dataserver name for some reason and want to output something like

 

select convert(varchar(30), getdate() ,113))+ @@servername as mystring

- you can't even run this because "error opening select * from " no columns returned

 

You can't even write a CTE to create a "fake" table:

;with dual as(
select null as dummy
)
select convert(varchar(30), getdate() ,113))+ @@servername as mystring
from dual

-- also fails

 

So in order to do this, I need to come up with a random table name which I redundantly select from, adding load to the dataserver - it feels very restrictive and I resent having to find workarounds to deal with these gaps in Alteryx functionality.

j4jackycheng
7 - Meteor

Is this idea still in "under review" status after 5 years?  

KylieF
Alteryx Community Team
Alteryx Community Team

Hi @j4jackycheng,

 

This idea was placed in the Under Review status back in January of 2018, while I understand 2 years is still a long time this idea also falls into a group of ideas the our product team is currently re-evaluating to provide updated statuses to. We appreciate your patience while we work through that batch of ideas and insure an accurate update can be provided.

DKR
5 - Atom

This would be very helpful to have.

 

Case:

I have a workflow that is copying from one database into a set of staging tables within another.  Once the data has been copied, I need to run a series of Stored Procedures in a specific order to do further matching within the database.

 

Being able to have the workflow complete by kicking off each stored procedure would be extremely helpful.

Michael_Draper
7 - Meteor

Another upvote for this functionality. As someone trying to "win over" teammates from legacy systems onto Alteryx, some more flexible options to send *any* SQL code to a target server would be a game-changer. Workarounds completely hamstring the entire strength of the program. Anything that can run SQL commands in-DB and allows the user to control when it happens (eg after some tools but before others) would be an incredible value-add. Can provide more concrete use cases if that's helpful.

 

A basic one is having to choose between "Create Table" and "Overwrite Table" - depending on our monthly processes we might need one or the other. Using a DROP TABLE IF EXISTS command fixes this immediately, but do not want this in a Pre-SQL statement that runs whenever I interact with the workflow, or Post-SQL with dummy output.

 

But that is just one example of many that will keep appearing. Please give us the ability to help ourselves while more functionality is added into the base tools!

YEM
8 - Asteroid

This is really needed.  I use the post-sql to run stored procedures all the time.  It'd be much easier if I can just fire off sql commands directly.

Josh_WI
6 - Meteoroid

Hello, I developed a workaround to this issue. Use a placeholder query that every user should have access to (information tables, select none from it, but use presql to run any desired sql (stored procs, table creation, index creation, etc) 

Inside Macro: Presql should replace 'SELECT NULL'

Josh_WI_0-1641852175425.png

 

Outside Macro: 

Josh_WI_1-1641852185970.png

 

Has been working well for our Data Lake, where table creation with indexes, temporal tables are all built using this component. Probably our #1 used component across our workflows. We also have a version that waits until the statement is complete so that another call can be done in a component after. Hope this helps somebody.