Good morning,
New to Alteryx (the product, and hence this community). After some beginners assistance please; I can't seem to solve an issue.
Overall objective: I want to take values from a spread sheet, that I can then use to retrieve data from a SQL server, and finally blend the two different sources. The spread sheet has unique identifiers in it, that I want to use to query the SQL server, because I don't want to return all the data.
For example:
Spread sheet has Unique IDs , 112233, 223344, 334455 - there will be thousands and they won't always be the same (i.e. I want to create a repeatable workflow that has different spread sheets as the source).
I then want to retrieve (select *) the 'product type' where the Unique ID is equal to 112233, 223344, 334455, and so on.
What is 'the best' way of taking all the unique IDs from the spread sheet and using those values to construct the select statement?
Appreciate the help and guidance.
Regards,
JD.
Solved! Go to Solution.
Hey JD. I'll briefly explain how I'd do it, then I'll point you to some helpful resources that show you other examples.
Helpful resources:
Absolutely spot on, thank you.
Got this working within minutes.
Thank you sir.
Hi Jon
Thanks for the detailed explanation/instructions - very useful.
Just a quick follow up question... I see there may be a limit in the "SQL IN Clause". What would be the recommened solution if we exceed the limit.
King Regards and Thank You.
Naresh
In regards to your question on the limitation for the "SQL IN Clause", this setting when checked will allow for a single query to be run (where your query actually contains an IN clause). It will basically combine a list of values brought into the Dynamic Input tool into a list for the IN clause. If the size limit is exceeded by the number of characters coming through the list, it will split up the query into multiple queries.
Check out Help for the details.
Not certain if this is the reason for this option, but there is a limitation (at least in MS SQL Server) of the characters allowed in a query statement...https://msdn.microsoft.com/en-us/library/ms143432.aspx
Hi John,
I implemented the same method as you have suggested below, but I can see in the output that the connection is establishing after every select query.
I use Hive connection(Cloud DB) and I see the below statement after every select query:
Dynamic Input (2) ODBC Driver version: 03.80
<select query with 1st where condition>
Dynamic Input (2) ODBC Driver version: 03.80
<select query with 2nd where condition>
Is there any way to include loop or something so that we can get rid of establishing DB connection again and again.
Hi Ruchi
I don't think you can just do one connection and subsequently use different queries using the initial connection.
I could be wrong but I believe you try to get as much information as you possibly can in the first connection (this way you reduce the number of odbc connection). I fully acknowledge that the performance may need to be balanced
Also..when you start the workflow, the connections are initialised at the beginning