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 Server Discussions

Find answers, ask questions, and share expertise about Alteryx Server.

How to run complex SQL query

Chriszou
8 - Asteroid

Hi Everyone, 

 

We are trying to use Alteryx to run a complex SQL query. Please see the code below. The query will create a table, then call a stored procedure and select all data to the table as last step. Does anyone has done something similar in the past ? Any suggestions on how to run this query thru Alteryx ?

 

USE [ABC]
GO

DECLARE @DateFrom date = '2019-12-01';
DECLARE @DateTo date = '2019-12-31';
DECLARE @PGID numeric(18,0) = 146;
DECLARE @OID numeric(18,0) = NULL;

CREATE TABLE #income_allocation
(
PGID numeric(18, 0) NOT NULL,
OID numeric(18, 0) NOT NULL,
clinic_id smallint NOT NULL,
provider_id numeric(18, 0) NOT NULL,
location_id numeric(18, 0) NULL,
date_start smalldatetime NULL,
date_end smalldatetime NULL,
beginning_balance money NULL
);

INSERT #income_allocation
EXECUTE dbo.usp_stored_procedure1
@DateFrom
,@DateTo
,@PGID
,@OID

select * from #income_allocation

 

 

Thank you very much. 

5 REPLIES 5
hroderick-thr
11 - Bolide

When I needed Alteryx to run a dynamically generated create view sql, I saved the sql script as a file and used run cmd tool to run a python program that input the file and ran the script. There's a command line option for all databases that might be used instead of python. The python tool has improved since I did this and may work better than the run command tool I used.

Chriszou
8 - Asteroid

Thanks for your idea. Is it possible you could share a sample workflow ? Also have a associated question. If the sql stored process have a few parameters and these parameters need to be changed in every run. How would you recommend to handle this ? (i.e. how to update the parameter at every run and feed to SQL from Alteryx). Thank you very much

hroderick-thr
11 - Bolide

I'm sorry but I don't have time to build an example and I can't share my examples from work.

Here's an outline of what you need to do....

1. Get command line tool for your database working in the environment it will be running (desktop, server, worker, etc)

2. Create a bat file that uses the command line tool to run your stored procedure with hardcoded parameters

3. Use Alteryx to create a bat file like you tested in 2, except with dynamic parameters

4. Use Alteryx Run Command tool to run the bat file you created in 3.

Tip: Put 3 and 4 in separate workflows and use Crew Runner macro to run them in order.

 

Chriszou
8 - Asteroid

Thank you for the advice. I will give it a shot. Again, thank you. 

hroderick-thr
11 - Bolide

Best wishes for success. If you get it working I'd love go get my first solved credit on this forum 🙂

Given a bit of encouragement I try some evening work to build a python based sql tool for the gallery.