Query of Query gives the developer that ability to manipulate a query result in memory. It is a real neat feature, but is very limited in what it can do and very quickly becomes extremely frustrating. It has a very limited range of SQL functions and syntax that almost makes it next to useless if you want to do anything serious with it. This blog post is a collection of things I have put to gather after many hours of scouring the 'net and even more trying to make it work. They include update and insert, sub-selects, and my latest - outer join.

I am using query QuerySim to create an populate a query to simulate a database table.

Everyone will no doubt be familiar with the normal SQL SELECT

<cfquery name="qryStudent" dbtype="query">
    SELECT id,fistname,surname,dob
    FROM VARIABLES.qryStudents
    WHERE id = <cfqueryparam cfsqltype="CF_sql_integer" value="#ARGUMENTS.id#" />
</cfquery>

To DELETE a record - slect everything except the record(s) to be removed

<cfquery name="VARIABLES.qryStudents" dbtype="query">
    SELECT #VARIABLES.fieldNames#
    FROM VARIABLES.qryStudents
    WHERE id != <cfqueryparam cfsqltype="CF_sql_integer" value="#ARGUMENTS.id#" />
</cfquery>

To save (INSERT or UPDATE) a record gets a little more interesting. As I mentioned, I am using QuerySim to create my query. I modified it slightly to accept datatypes as well, and I pass these in as variables.

    <cfset VARIABLES.fieldNames = 'id,fistname,surname,dob' />
    <cfset VARIABLES.fieldTypes = 'Integer,varchar,varchar,date' />

You would have noticed that I used VARIABLES.fieldNames in the DELETE SQL.

Passing data types to CreateQuery() will stop ColdFusion from guessing what the data types are and potentially screwing things up for you. Using a list of field names has a few benefits: it ensures fields are named, rather than select *; ensures fields are always selected in the same order; and you can programatically loop through them.

Rather than create sample code and potentially add mistakes, I will just show you my code that I have working

    <cffunction name="save" access="public" output="false" returntype="void">
        <cfargument name="createDate"  type="string" required="false" default="#Now()#" />

        <cfset var field       = '' />
        <cfset var fieldValues = '' />
        <cfset var qryMaxItems = QueryNew('id', 'integer') />
        <cfset var pk          = 0 />

        <cfif ARGUMENTS.endDate NEQ "">
            <cfset ARGUMENTS.endDate = ParseDateTime(ARGUMENTS.endDate) />
        <cfelse>
            <cfset ARGUMENTS.endDate = DateAdd('d', 14, Now()) />
        </cfif>

        <cfif ARGUMENTS.id EQ 0>
            <cfquery name="qryMaxItems" dbtype="query">
                SELECT MAX(id) as id
                FROM VARIABLES.qryItems
            </cfquery>
            <cfset pk           = qryMaxItems.id />
            <cfset ARGUMENTS.id = pk + 1 />
        <cfelse>
            <cfset pk = ARGUMENTS.id />
        </cfif>
        <cfquery name="VARIABLES.qryItems" dbtype="query">
            SELECT #VARIABLES.fieldNames#
            FROM VARIABLES.qryItems
            WHERE id != <cfqueryparam cfsqltype="CF_sql_integer" value="#ARGUMENTS.id#" />
            UNION
            SELECT
            <cfloop from="1" to="#ListLen(VARIABLES.fieldNames)#" index="field">
            <cfqueryparam cfsqltype="CF_sql_#ListGetAt(VARIABLES.fieldTypes, field)#" value="#ARGUMENTS[ListGetAt(VARIABLES.fieldNames, field)]#" /> as #ListGetAt(VARIABLES.fieldNames, field)#
            <cfif field NEQ ListLen(VARIABLES.fieldNames)>,</cfif>
            </cfloop>
            FROM VARIABLES.qryItems
            WHERE id = <cfqueryparam cfsqltype="CF_sql_integer" value="#pk#" />
        </cfquery>
    </cffunction>

Today I run into an issue that previously would have made me give up - OUTER JOIN. But based on what I have cobled together so far I thought I would be able to solve it. But before I could I had to solve another issue - SUB-SELECT.

A sub select is where you have WHERE id NOT IN (select ID FROM anotherTable). Whilst QoQ dooes support IN, it does not like a SQL in there. But you can use ColdFusion functions within a QoQ.

    <cfquery dbtype="query" name="qryClass">
    SELECT name
    FROM VARIABLES.qryClasses
    WHERE studentID NOT IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#ValueList(VARIABLES.qrySudents.id)#" list="true"  />)
    </cfquery>

An OUTER JOIN is when you join multiple tables, with one table that may not have a record match for the other table, but you still want to see the data for that tables. What you end up with is NULLS for the values of the other table. What I ended up doing is doing a UNION, the first SELECT is all the results that match, and the second is all the values from the main table that were not in the first select, and empty stings for all the fields of the outer table, and then an ORDER BY. Once again, I will show working code.

<cfquery dbtype="query" name="qryCompanies">
 select qryCompanies.security as security,
        qryCompanies.exchange as exchange,
        qryCompanies.description as description,
     qryCompanies.companyName as companyName,
     qryCompanies.SECTYPE as secType,
     qryCompanies.GROUPTYPE as groupType,
     qryCompanies.gicsCode as gicsCode,
     qryCompanies.gicsName as gicsName,
     qryCompanies.gicsColourCode as gicsColourCode,
     qryCompanies.gicsParentColourCode as gicsParentColourCode,
     qryCompanies.gicsGroup as gicsGroup
 from qryCompanies,
      qryHoldings
 where qryCompanies.security = qryHoldings.security
   and qryCompanies.exchange = qryHoldings.exchange
   and (qryHoldings.AccountNumber = <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(ARGUMENTS.AccountNumber)#">)
 </cfquery>
 
<cfquery dbtype="query" name="qryCompanies">
select qryHoldings.accountNumber as accountNumber,
       qryHoldings.asOf as asOf,
    qryHoldings.exchange as exchange,
    qryHoldings.portfolioType as portfolioType,
    qryHoldings.security as security,
    qryHoldings.totalCost as totalCost,
    qryHoldings.unitCost as unitCost,
    qryHoldings.units as units,
    qryCompanies.description as description,
    qryCompanies.companyName as companyName,
    qryCompanies.SECTYPE as secType,
    qryCompanies.GROUPTYPE as groupType,
    qryCompanies.gicsCode as gicsCode,
    qryCompanies.gicsName as gicsName,
    qryCompanies.gicsColourCode as gicsColourCode,
    qryCompanies.gicsParentColourCode as gicsParentColourCode,
    qryCompanies.gicsGroup as gicsGroup
from qryCompanies,
     qryHoldings
where qryCompanies.security = qryHoldings.security
  and qryCompanies.exchange = qryHoldings.exchange
 
UNION
 
select qryHoldings.accountNumber as accountNumber,
       qryHoldings.asOf as asOf,
    qryHoldings.EXCHANGE as exchange,
    qryHoldings.portfolioType as portfolioType,
    qryHoldings.security as security,
    qryHoldings.totalCost as totalCost,
    qryHoldings.unitCost as unitCost,
    qryHoldings.units as units,
    '' as description,
    '' as companyName,
    0 as secType,
    '' as groupType,
    '' as gicsCode,
    '' as gicsName,
    '' as gicsColourCode,
    '' as gicsParentColourCode,
    '' as gicsGroup
from qryHoldings
where qryHoldings.security not in (<cfqueryparam cfsqltype="cf_sql_varchar" value="#ValueList(qryCompanies.security)#" list="true"  />)
</cfquery>

Notice that secType has zero rather than an empty string; that is because it is an integer, not a varchar. I am going to see if I can get a NULL in there - should be able to do something with a JAVA type. If I can get that going I will come back and clean up these examples.

This is a quick dump of what I have at least for my own records as no doubt I will have forgotten how and where I did all this by the next time I need it. If you have found it useful, or have any suggestions, please leave a comment.