The topic of this post is pretty simple, you can use variables anywhere in your query.  It doesn’t just have to be in the Where clause.  You can use them for the table name you want to query from.  You could even use them for the columns in the select statement too if the need was there.  To get to the why of when you may want to use variables this way I need to go down the rabbit hole of a request I just had to build.  Hopefully it will help give an idea of how this flexibility helps and how you might make use of it.

I was tasked to build a site that would display a bunch of results from different surveys.  Each page would look the same and have a right sidebar with some definitions and descriptions, then a chart of the survey data on the left, with a data table underneath to show the data in tabular form.  It seems pretty straightforward but surveys have these pesky things called scales with their answers.  Those scales can change from survey to survey.  Sometimes the answers are yes or no, sometimes they are strongly disagree to strongly agree, never to always, and so on.

So we have a standard format that we want to display all these survey results but how can one account for all of these different answer scales without making a query for each survey.  Then you have to account for the graphs and tables changing from survey to survey too.  So there is a lot going on here and I’ll touch on the latter points but the focus here is on the query problem.

The first thing I did was make a table for each survey’s responses.  They have a couple common columns in each survey table but the rest of the columns are unique to each survey and it’s scale.  Each possible answer has two columns.  One which holds the number of responses and one with the percent of total responses.  For example using a Yes/No scale, the table has the common columns and then Yes_N, Yes_Pct, No_N, and No_Pct.  _N columns hold the number of responses and _Pct holds the percent of total responses.  This naming convention is carried over across all of the survey tables.  So that solves how to store the survey data.  Having a shared standardized format is a key down the road when we display the data.  Next, how do we get to the data without multiple queries.  The answer is I make a separate table that holds information about each survey.  In this table I have a column named “responseTable” that holds the table name with the survey data we need.  I use the primary key from this table to link to the data in the answers table because sometimes different surveys can have the same answer scale and those should be stored together to use the database efficiently.

So when I pull up the page where I need to pull the data to display I use the ID passed in to get the row out of the survey description table.

qrySurveyDetails = queryExecute(‘Select name, description, responseTable from surveyDescription Where pkey = :surveyKey’, {   surveyKey = { cfsqltype = ‘cf_sql_varchar’, value = surveyID } }, { datasource = dsnName } );

Now that I have the table name I can go out to get the survey data itself.  This is where I use the variable in a query creatively.

qrySurveyData = queryExecute(‘Select * from #qrySurveyDetails.responseTable# Where surveyDescription_pkey = :surveyPkey’, { surveyKey = { cfsqltype = ‘cf_sql_varchar’, value = surveyID }}, { datasource = dsnName } );

Yeah, I used select * which you really should avoid as much as possible.  Pull what you need and not everything.  But this is a special case, remember that we do not know the column names because the scales can change from survey to survey.  What we have done right now is setup a structure where with two queries we can get the responses from any survey and we are good to go.  We don’t need a query for each type of survey anymore.  Now I have simplified this a bit and you may have some ideas in your head of things we’ll also need.  I’m just focusing on using variables in the queries in different ways.  You do need to do some more work to really dial this all in.  For example, I have a lookup table out there that turns the standardized column names such as “StronglyDisagree_N” to plain English “Strongly Disagree”.

So that’s a real world case of how to someone can use variables in more than the Where clause to solve some tricky problems.  Now I did say I would touch on the tables/graphs display because another thing you probably noticed is I used “Select *” because I didn’t know the column names.  The answer is that with queries I have access to the column names by using #qrySurveyData.ColumnList#.  I filter out the common columns among the tables and then just loop the rest and use my lookup table to translate them to plain English.  When I am outputting the query I leverage the ability to treat a query like a structure and again use variables creatively.

Here’s a quick simplified example of how to do that:

for ( i=1; i<= qrySurveyData.recordCount; i++ ) {

for ( column in qrySurveyData.columnList ) {

// filter out those common columns

                     if ( !listfindnocase( ‘pkey,createdOn,createdBy’, column ) )

                                        writeOutput( qryTempData[column][i] );

            }

}