A look into the options of dynamic SQL queries and their applied uses Preparing the SQL query The BEGINREPLACE and ENDREPLACE strings will be our targets for string replacement Preparing the Parameter CSV ID: 1044578
Download Presentation The PPT/PDF document "Dynamic Input with SQL Queries" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
1. Dynamic Input with SQL QueriesA look into the options of dynamic SQL queries and their applied uses.
2. Preparing the SQL queryThe BEGIN_REPLACE and END_REPLACE strings will be our targets for string replacement.
3. Preparing the Parameter .CSVUsing a CSV to establish parameters is a great way to configure the dynamic input for your SQL query. In this example, we will be making a report that gathers information from the previous week’s data. In order to do this we need to establish parameters based on what day of the week it is.
4. Configuring the module.Begin by setting up a simple filter with a date time condition. Here we are going to use DateTimeFormat() to produce the day of the week and compare it against our parameters.
5. Set up the queryClick edit, set up your connection, and paste in your SQL query.
6. Time to set up the querySelect “Replace a Specific string” and we will Establish the conditions of String replacement.
7. Establish the string replacementWe have 2 targets set up to be used for replacements. Each target must be typed just how it is in the query and make sure that you did not use a word that you will use elsewhere in the query by mistake.
8. Incorporating the Summary ToolIn a case where we want to look at just certain records and we have a list, the summary tool can also be incorporated in this method. In this example, we will be using a .csv file, however any format that you can get into Alteryx will work, whether it’s a alteryx database file or an output from a SQL query.
9. PreparationIt’s important to remember that we are adding this directly to a SQL query, so, syntax is very important. Make sure that you put your target field in quotes using the Formula tool.
10. ConcatenationAdd your Summary tool, then click Add > String > Concatenate. Make sure your separator is set to a comma so it functions correctly in the Where Clause.
11. Set up Query in Dynamic Input toolSet up your query with your target string. This time we will be targeting CASE_REPLACE
12. Multiple TechniquesWe can eliminate the Formula tool entirely by building it’s functions into the Summary tool’s concatenate functions. By using an apostrophe in start and end, as well as putting the apostrophes around the comma separator, we can actually achieve the same effect without using a formula tool.