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

SP and SQL queries

vaishalilambe17
8 - Asteroid

Hi,

 

I am trying to run below thing in input tool :

  1. Select Distinct type_i  From master_personnel_dtls Where master_personnel_dtls.pln_i = 'ESOP'
  2. SELECT  CONVERT(DATETIME,CONVERT(VARCHAR(10),DATEADD(dd,-1*DAY(DATEADD(mm,-1,GETDATE()))+1,DATEADD(mm,-1,GETDATE())),101)) first_day_prior_month,DATEADD(ss,-1,CONVERT(DATETIME,CONVERT(VARCHAR(10),DATEADD(dd,DAY(GETDATE())*-1+1,GETDATE()),101)))  last_day_prior_month
  3. EXEC rpt_web_ord_exec_wrapper'XXXXX', '03/01/2019', '03/31/2019', 'CROSSPRODUCT', 'ABNH,AIM,BON,BONU,CONV,DCPL', 'REF_NO', '5'

Output of first 2 is input for SP. Please see and guide how I can do it. I need to do it asap as I spent enough time finding out. No help found yet.

9 REPLIES 9
jamielaird
14 - Magnetar

Hi @vaishalilambe17 

 

If I understand correctly you want to run two SELECT statements and then include the results of these in an EXEC statement that invokes a stored procedure.

 

To run the SELECT statements you simply need to configure two Input tools. Your second select statement doesn't actually reference a table and you are simply using SQL to work out the last day of the prior month. You can find simpler ways to do this using a Formula tool (the DateTime functions are well documented). So that leaves you with only one Input tool.

 

To invoke the stored procedure use a Dynamic Input tool. Again, this is well documented and you should be able to modify your SQL statement to include the values from the previous steps of the workflow.

 

Your final workflow will look something like this:

 

  1. An Input tool
  2. A Formula tool
  3. A Dynamic Input tool

Since you're connecting to databases there's no way I can replicate this for you, but you will learn the most by doing this for yourself anyway. Please feel free to post up if you have questions as you build the workflow.

 

jamielaird
14 - Magnetar

I couldn't resist ... here's how you'll find the last day of the prior month using a Formula tool:

 

Screenshot 2019-05-07 at 18.27.33.png

vaishalilambe17
8 - Asteroid

@jamielaird  Thank you so much for your reply. I am getting no columns returned. PFA my workflow

vaishalilambe17
8 - Asteroid

@jamielaird 

 

The thing is three values for the stored procedure are ffrom sql queries but remainig values are static. If i insert from formula toll it created those many records as my first query and table. PFA screenshot.

Also I am not understandinf, even though I am doing update sP for each column its giving me error opening procedure. Hw can I connect to SP n dynamic tool?

jamielaird
14 - Magnetar

Hi @vaishalilambe17 

 

Your method looks OK to me, but you'll need to use unique placeholder values in the template SQL query within the Dynamic Input tool rather than NULL (since all of these will be replaced with the value of the first replacement).

 

Screenshot 2019-05-07 at 19.49.41.png

vaishalilambe17
8 - Asteroid

@jamielaird what is unique placeholder value?

jamielaird
14 - Magnetar

Something like 'value1', 'value2', 'value3' etc instead of 'NULL', 'NULL', 'NULL' etc.

 

When the SQL code is updated it is running a Find & Replace on the query text so you need to make sure you have a unique key for each value you want to replace.

vaishalilambe17
8 - Asteroid

Thanks

vaishalilambe17
8 - Asteroid

stilll have issue like PFA screenshot

Labels