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!

Alteryx Designer Desktop Discussions

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

Multiple Queries In-Database

robertfishel
8 - Asteroid

Almost Friday, Alteryx Community.  Can. You. Feel. IT!?!?!?!?!?!?!?!?

 

 

So i am just starting to get into the In-Database tools.  I have a SSMS query that is really two queries, one being a query that creates a temp table, which is then itself queried.  Confused yet.

 

So in Alteryx, I have successfully used the Connect In-DB tool to bring back the desired results of the first part of  my query (the temp table part).  Now, I am wondering how I would run the second part, which queries the temp table?  Is it one of the In-Database tools, or am I going about doing this the wrong way?  Can you create a temp table using the In-Database tools, just like you would in SSMS?  Thanks. 

11 REPLIES 11
MarqueeCrew
20 - Arcturus
20 - Arcturus

@robertfishel,

 

While you can materialize the data going into the temp table, if you're not going to use that data for long you don't need to do so.  If you use the in-db tools, you can stream that data and join to it with your second query.  That temp table will get created without your knowledge if and when it is needed.  Keep your data in-db as long as you can and don't write it out unless you've got a really good reason to do so.  When you JOIN data in-db, you'll be creating a temp table.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
robertfishel
8 - Asteroid

Mark, thanks for the quick reply.  I am just not sure which tool(s) to use to accomplish what you are outlining in your answer.

cmcclellan
13 - Pulsar

Mark is basically saying:  don't worry about it, things will work fine :)

 

If you think you must have a temp table (you don't, Alteryx creates temp tables all the time for in-db), you can use the Write Data in-DB tool to create a table of your choosing or a temp one that Alteryx names ... 

 

2018-12-14 07_54_18-Alteryx Designer x64 - New Workflow1_.png

robertfishel
8 - Asteroid

Ok, so I am trying to follow along here....

 

In SSMS:

My first query creates a temp table (in memory, I am assuming).  Then my second query "queries" the temp table.

 

In Alteryx:

I use an In-Database tool to run my first query, which puts the "temp" data..... where?   Then, I use a _____ tool to query that "temp" data.

 

Am I looking at this the right way, or does Alteryx not do things as I think?  

 

Also, i do not have permission to Write in the database, so that option is out :(

Claje
14 - Magnetar

Hi!

Let me try and help out:

Take the SELECT query that you are using to run the first query, and put it in a "Connect In-DB" tool.


If your second query only uses results from the first which creates a temp table, try using the Filter, Formula, Select, and Summarize In-DB tools to recreate that query.


If your second query JOINS to your temp table, take the piece of query 2 which selects fields from tables that are NOT your temp table, and put that in a second "Connect In-DB" tool.  Then, use a "Join In-DB" tool followed by a "Select In-DB" (and possibly a "Filter In-DB") tool to perform the Join you are using in your current SQL.

cmcclellan
13 - Pulsar

@robertfishel wrote:

 

 

Also, i do not have permission to Write in the database, so that option is out :(


I'm pretty sure you need write permissions to use in-DB tools, because it creates tables (albeit temp, but it still has to create tables)

robertfishel
8 - Asteroid

Thanks everyone for your responses.  @Claje, I am going to try the steps as outlined.  I appreciate your help.

robertfishel
8 - Asteroid

Do you know if it is possible to have a nested select statement in Alteryx?  A simple structure being like:

 

SELECT a.name FROM

(SELECT name FROM agentinformation) a 

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

If you place a SELECT tool after the Connect In-DB, you'll achieve that result.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels