topic How to run complex SQL query in Alteryx Server Discussions

How to run complex SQL query

Chriszou — Wed, 26 Feb 2020 04:26:40 GMT

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.

Re: How to run complex SQL query

hroderick-thr — Wed, 26 Feb 2020 12:53:23 GMT

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.

Re: How to run complex SQL query

Chriszou — Wed, 26 Feb 2020 14:41:59 GMT

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

Re: How to run complex SQL query

hroderick-thr — Wed, 26 Feb 2020 15:26:43 GMT

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.

Re: How to run complex SQL query

Chriszou — Wed, 26 Feb 2020 15:44:51 GMT

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

Re: How to run complex SQL query

hroderick-thr — Wed, 26 Feb 2020 15:57:52 GMT

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.