Query Builder

Follow these steps to create, edit and test a CFSQL-driven query:

1. Decide what type of query will be used. ClearFactr supports three modes: Generic Select, Find Row, and Stored Procedure.
   • Find Row is a simplification of Generic Select and conceptually is similar to a VLOOKUP call. Use it in the case where you know that each row in a table has a unique identifier, and your intention is to return properties from that single row.
   • Stored Procedures, if supported by your Cloud Data Warehouse, also have a simpler interface in that you only need to specify the parameters that your chosen procedure requires.
   • We'll focus on the Generic Select here, which covers concepts that can be applied to all three.
2. Choose your DataSource from the selector in the upper right corner of the Builder. Based on that choice, the Builder will offer successive levels of drilldowns into whatever Catalogs, Databases, and Tables are available within each. In the example above, we've selected a table called 'mortgages'.
3. Note the field names on the left, and the resulting SQL below the Test button. Indeed, ClearFactr is writing SQL for you under the covers, and these fields serve to specify the resulting SQL, and most importantly, give you the opportunity to drive the query with other cell values in your model.
4. In the Select field, specify what table properties you want to return. You can type them by hand, or insert them via clicking on field names in the Table structure inspector at the right. Note that any valid SQL query expression can be used here, including utilizing server-side functions.
5. In the From field, specify one or more tables. If using two or more, with the intention of creating a join, specify your aliases here, too 

The Filters section is where you will begin to limit the results that actually come back to the ClearFactr grid(s). There are two fields that collectively will be concatenated into a WHERE clause, each of which can be arbitrarily complex. Depending on the length of your expressions overall, split them up for legibility purposes between the Primary and Secondary fields. Note the syntax in the example above:

1. The contents of each field are actually expressed in Excel-style syntax. This allows for cell values to be incorporated by value into the strings that will ultimately be used in the SQL.
2. Note, for example, the Primary field above: The string "state =" is going to refer to the 'state' column in the mortgages table, but the actual state value is going to come from the evaluated cell D1. Notice how these things are concatenated together with the Excel-style '&' operator.
3. Lastly, notice how this translates into "state = 'DE'' within the SQL, because the currently value of cell D1 happens to be 'DE' (Delaware).
4. Likewise with the Secondary field, the number of rows to limit the output to is coming from the cell A2. In the resulting SQL we see that is 10.

Lastly, the Other field will also get concatenated. This is a good place for expressing HAVING clauses.

As mentioned above, as you work, you'll see the resulting SQL in the field just below the Test button. Click Test when you're ready. The results there will always be limited to a single row. It's a great sanity check to assure that your query is behaving as desired.

When you're happy with the results, click the Commit button to generate and insert the CFSQL function into the cell, and thus into your model.

Frequently Asked Questions