How Do I Create a Select Statement With the SQL Wizard?

In this article, we'll go step by step through an example of using the SQL Wizard to create a select statement for a ForEach Tag. If you aren't familiar with the ForEach Tag, please review our ForEach Tag Reference before continuing.

In this example we'll create a table of products whose unit price is greater than $50, sorted by the number of units in stock. You can begin with a blank Word document, or follow along with the attached example template, which uses our public mssql.windward.net data source.

Seven_Steps_to_Using_the_SQL_Wizard.docx

Seven Steps to Using the SQL Wizard

Although this example is specific to a ForEach Tag, this procedure can be followed to create a SQL select statement for any Tag returning data from a SQL data source.

  1. Create the Tag where the select statement will be applied
  2. Bring up the SQL Wizard
  3. Select the columns you wish to display data about in your output
  4. Create a condition the data must satisfy to be displayed
  5. Sort the data satisfying the condition (optional)
  6. Connect Out Tags to the new ForEach Tag select statement
  7. Generate output

Create the Tag Where the Select Statement Will be Applied

We've opened a Word document and connected to our example Northwind database in our public mssql.windward.net SQL Server. Then we inserted a two-row table with columns for product ID, category name, product name, unit price and units in stock.

Next, we insert a ForEach Tag, and Out Tags for each column. We'll build the select statement for the ForEach Tag with the SQL Wizard.

Bring Up the SQL Wizard

To bring up the SQL Wizard on the ForEach Tag, select the ForEach Tag, then click on the Wizard button on the AutoTag tab of the Word ribbon.

Select the Columns You Wish to Display Data About in Your Output

Drag and drop from the Data Pane the columns you wish to return from the data source. 

Create a Condition the Data Must Satisfy to Be Displayed

We want to display those products whose unit price is greater than $50. To do so we must create a condition. A condition is a node (or column), a comparison and a value, such as "unit price > 50".

First, click on "click here to add a group":

Then click on "click here to add a filter":

Next, click on "click here to select a node", then select the UnitPrice column in the popup window:

Click on "equal to", and change it to "greater than":

Then click on "click here to set the value", and enter "50":

Sort the Data Satisfying the Condition (Optional)

Now we have a select statement which will return the data we wish for each product with a unit price greater than $50. To sort those product IDs by units in stock, drag and drop the UnitsInStock column from the Data Pane onto the Sort area:

Click on "Products.UnitsInStock sorted A-Z", change it to "sort Z-A", then click on "OK":

Now double-click on the ForEach Tag to bring up the Tag Editor, and in the Query Pane you'll see the select statement we just built using the Wizard.

Connect Out Tags to the New ForEach Tag Select Statement

Now that we're finished creating our new select statement, we must connect the Out Tags in our table columns to the ForEach Tag:

  • Double-click on the Out Tag in the Product ID column to bring up the Tag Editor
  • Drag 'n' drop the ProductID column from the Data Pane onto the Query Pane
  • Save the Out Tag
  • Repeat for the other Out Tags, using the appropriate columns in the Data Pane

Your template should look like this:

Generate Output

Optionally, you can add some Word formatting such as a table style and text alignment. Also, change the formatting of the [UnitPrice] Out Tag so it displays values as currency. Then generate output!