cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Discover peer insights or crowdsource your one of a kind Designer question.

Executing already available SQL Query in SQL table with Alteryx

I have a SQL table and one of the column in that table has SQL queries (Approx 1200 queries). I want to loop through each cell in that column and execute the SQL queries. Once a query is executed I want to get the output on excel and then go to the next query and execute the same and get the results on excel to a different tab. Is this something I can achieve using Alteryx?

Alteryx Certified Partner

Yes, this should be very possible using Alteryx. A batch macro will likely be necessary in this case, and it looks like you knew that since you added that tag to your post. Since your field configuration from each query might differ, I think it would be best to have your Output tool inside your batch macro. 

 

There are a couple ways you could build your batch macro. You could start with a Text Input (later converted to a Macro Input) with one of the queries that connects to a Dynamic Input tool to execute. Otherwise you could use the Control Parameter tool to update the query in an Input tool. Don't forget to include the Excel file path, file name, and sheet name you want with each query you send into the macro. The file name in the Output tool should be "<file path>\<file name>.xlsx|<sheet name>"

 

It isn't really feasible to build an example without access to the same database, so just let us know if you have any questions. 

Hi Charlie,

 

The columns resulting from each query are the same.

I created a batch macro and inserted that batch macro into a dynamic input workflow. However, when I execute the workflow it gives me a series of errors.

 

Error: BatchMacro (8): Record #1234: Tool #3: The field "Select '4571'AS RuleId,fc.SnapshotKey,fc.FinStatusCd,fc.GLSrcSysId,fc.LoanSysId,fc.HeldType,COUNT(fc.LoanNum) as LoanCnt,SUM(fc.PrinBal) AS PrinBal,SUM(fc.BookValAmt) AS BookValAmt,SUM(fc.PrinBal) / 1000000 AS PrinBalInMil,SUM(fc.BookValAmt) / 1000000 AS BookValAmtInMil from LynxReporting.dbo.factPortCore (NOLOCK) fc Inner join LynxSnapshot.dbo.LoanWorkout (NOLOCK) lw on fc.SnapshotKey = lw.SnapshotKey and fc.LoanNum = lw.LoanNum where fc.SnapshotKey = (Select MAX(SnapshotKey) from LynxReporting.dbo.factPortCore (NOLOCK)) and fc.FinActiveFlag = 'Y' and lw.LastTreatmentCompleteDt >'2015-12-31'--LoanMod Process Implementation Date-- and lw.ModCnt IN (0,1) and lw.PrevValidationDt <> '1900-01-01' Group By fc.SnapshotKey,fc.FinStatusCd,fc.GLSrcSysId,fc.LoanSysId,fc.HeldType" is not contained in the record. (Expression #1)

 

Attached zip folder has the macro and the workflow for your reference. I am on fire right now and would really appreciate all your help.

 

Regards,

Jitender

Attached Batch Macro

Alteryx Certified Partner

Since we don't have access to the same databases, I built an example as best I could to show how this process could work. Take a look at the attached example and let me know if you have any other questions.

Hi Charlie,

 

Appreciate all your help with this. This gets me close to what I need however I am still getting an error -

Taking about the batch macro you created -

1. The macro input works fine. I am using it to get the entire SQL table.

2. I have added dynamic input which just reads the column FinDQQueryTxt_Std. This is the column that has multiple SQL queries which I want to execute. This is where I am getting the error now

 

 Designer x64 Finished running 20180329-BatchQuery.yxmc in 28.3 seconds with 1 error and 1 warning

 

 Dynamic Input (2) Error opening "SELECT '1056' AS RuleId___   ,fc.SnapshotKey___   ,fc.FinStatusCd___   ,fc.GLSrcSysId___   ,fc.LoanSysId___   ,fc.HeldType___   ,COUNT(fc.LoanNum) AS LoanCnt___   ,SUM(fc.PrinBal) AS PrinBal___   ,SUM(fc.BookValAmt) AS BookValAmt___   ,SUM(fc.PrinBal) _ 1000000 AS PrinBalInMil___   ,SUM(fc.BookValAmt) _ 1000000 AS BookValAmtInMil__FROM LynxReporting.dbo.factPortCore AS fc (NOLOCK)__INNER JOIN LynxReporting.dbo.factPortAlt AS fa (NOLOCK) ON fa.LoanNum = fc.LoanNum AND fc.SnapshotKey = fa.SnapshotKey__WHERE fc.SnapshotKey = (SELECT MAX(SnapshotKey) FROM LynxReporting.dbo.factPortCore (NOLOCK))_AND fc.FinActiveFlag = 'Y'_AND (fa.ModCnt _ 0 OR fa.PostModNonPerformingCnt _ 0)_AND fc.HeldType __ 'REO'_AND fa.TDRAcctingTreatmentComplDt = '1900-01-01'_GROUP BY fc.SnapshotKey, fc.FinStatusCd, fc.GLSrcSysId, fc.LoanSysId, fc.HeldType__ORDER BY fc.SnapshotKey, fc.FinStatusCd, fc.GLSrcSysId, fc.LoanSysId, fc.HeldType||Select * From AdHoc.dbo.FinDQStdRules": No Columns Returned.

 

 Dynamic Input (2) The file "aka:MtgHUB|SELECT '1056' AS RuleId___   ,fc.SnapshotKey___   ,fc.FinStatusCd___   ,fc.GLSrcSysId___   ,fc.LoanSysId___   ,fc.HeldType___   ,COUNT(fc.LoanNum) AS LoanCnt___   ,SUM(fc.PrinBal) AS PrinBal___   ,SUM(fc.BookValAmt) AS BookValAmt___   ,SUM(fc.PrinBal) _ 1000000 AS PrinBalInMil___   ,SUM(fc.BookValAmt) _ 1000000 AS BookValAmtInMil__FROM LynxReporting.dbo.factPortCore AS fc (NOLOCK)__INNER JOIN LynxReporting.dbo.factPortAlt AS fa (NOLOCK) ON fa.LoanNum = fc.LoanNum AND fc.SnapshotKey = fa.SnapshotKey__WHERE fc.SnapshotKey = (SELECT MAX(SnapshotKey) FROM LynxReporting.dbo.factPortCore (NOLOCK))_AND fc.FinActiveFlag = 'Y'_AND (fa.ModCnt _ 0 OR fa.PostModNonPerformingCnt _ 0)_AND fc.HeldType __ 'REO'_AND fa.TDRAcctingTreatmentComplDt = '1900-01-01'_GROUP BY fc.SnapshotKey, fc.FinStatusCd, fc.GLSrcSysId, fc.LoanSysId, fc.HeldType__ORDER BY fc.SnapshotKey, fc.FinStatusCd, fc.GLSrcSysId, fc.LoanSysId, fc.HeldType||Select * From AdHoc.dbo.FinDQStdRules" has a different number of fields than the 1st file in the set and will be

skipped

 

Will really appreciate your usual guidance and help.

 

Regards,

Jitender

Alteryx Certified Partner

I'm happy to help, @JitenderChawla.

 

Alteryx isn't very descriptive when it comes to query errors. I recommend copying that query from the error message into your database tools program and try to execute it there. Most of the time when I get a generic "no columns returned" error in Alteryx, I can take the query to SSMS (we use Microsoft SQL Server) and it will explain why the query errors.

 

As for the second item, the Dynamic Input tool wants all results to have the exact same structure (field name, field types, etc). I wasn't sure if all the questions you're submitting would have the same output structure or not. If you need queries that output different data structures to work, the solution gets a bit more complex. I suggest getting the first error resolved, then we can talk about this if it's still an issue.

I already tried executing the code in SSMS and it ran fine without any error and also gave result. For the second error I will make sure all the queries generate similar number of columns