My QofQ Trick
Posted By : todd sharp Posted At : July 14, 2006 1:18 PM Posted In: SQL, ColdFusion
0
In the spirit of a few recent posts sharing tricks for query of queries, here's a technique that I like to use.
Basically I take advantage of the fact that having a SQL Server datasource allows us execute code that has absolutely nothing to do with the tables in that datasource. You can create temp tables and query those tables using the full range of TSQL functions and commands without the restrictions of QofQ. When would you do this? Let's say for example you are in one of those fun environments where you have to query from multiple datasources that may not be the same db type (Access, MySQL, etc).
Here's an example. The code below would normally perform a simple query against the cfcodeexplorer datasource:
from artists as a
left outer join art as b
on a.artistid = b.artistid
So how can we recreate this functionality? Start first with two queries - one pulling from artists and one from art.
SELECT artistid, firstname + ' ' + lastname as name
FROM artists
ORDER BY artistid
</cfquery>
<cfquery datasource="cfcodeexplorer" name="art">
SELECT artistid, artname, description, price
FROM art
ORDER BY artistid
</cfquery>
Now create the syntax for the variable table that will house the results of each of the queries. I store these in variables for now.
declare @tblArtists
table(artistid int, name varchar(100))
<cfoutput query="artists">
insert into @tblArtists
(artistid, name)
values
("#artistid#", "#name#")
</cfoutput>
</cfsavecontent>
<cfsavecontent variable="artTmpTbl">
declare @tblArt
table(artistid int, artname varchar(500), description varchar(500), price int)
<cfoutput query="art">
insert into @tblArt
(artistid, artname, description, price)
values
("#artistid#", "#artname#", "#description#", "#price#")
</cfoutput>
</cfsavecontent>
Now here's the beautiful part. Create another query, this time pointing to a valid SQL server datasource, run the temp table creation scripts and query against them.
/*From Transact-SQL Reference:
When SET QUOTED_IDENTIFIER is OFF (default), literal strings in
expressions can be delimited by single or double quotation marks.
If a literal string is delimited by double quotation marks,
the string can contain embedded single quotation marks,
such as apostrophes.*/
SET QUOTED_IDENTIFIER off
/*using double quotes in the tmp tbl inserts
and setting the quoted_identified off
eliminates the need for preserveSingleQuote
which would throw an error if any fields have
embedded single quotes*/
#artistsTmpTbl#
#artTmpTbl#
select *
from @tblArtists as a
left outer join @tblArt as b
on a.artistid = b.artistid
SET QUOTED_IDENTIFIER on
/* since my sql server setting is "on",
i set the quoted_identifier option back to on */
</cfquery>
Obviously this is NOT the most efficient code, but it demonstrates the ability to combine data from multiple diverse datasources that we might not otherwise be able to accomplish. Here's the complete code with a dump at the end. Just plug in a valid datasource in the final query.
from artists as a
left outer join art as b
on a.artistid = b.artistid --->
<cfquery datasource="cfcodeexplorer" name="artists">
SELECT artistid, firstname + ' ' + lastname as name
FROM artists
ORDER BY artistid
</cfquery>
<cfquery datasource="cfcodeexplorer" name="art">
SELECT artistid, artname, description, price
FROM art
ORDER BY artistid
</cfquery>
<cfsavecontent variable="artistsTmpTbl">
declare @tblArtists
table(artistid int, name varchar(100))
<cfoutput query="artists">
insert into @tblArtists
(artistid, name)
values
("#artistid#", "#name#")
</cfoutput>
</cfsavecontent>
<cfsavecontent variable="artTmpTbl">
declare @tblArt
table(artistid int, artname varchar(500), description varchar(500), price int)
<cfoutput query="art">
insert into @tblArt
(artistid, artname, description, price)
values
("#artistid#", "#artname#", "#description#", "#price#")
</cfoutput>
</cfsavecontent>
<cfquery datasource="impact" name="outerJoinQofQ">
/*From Transact-SQL Reference:
When SET QUOTED_IDENTIFIER is OFF (default), literal strings in
expressions can be delimited by single or double quotation marks.
If a literal string is delimited by double quotation marks,
the string can contain embedded single quotation marks,
such as apostrophes.*/
SET QUOTED_IDENTIFIER off
/*using double quotes in the tmp tbl inserts
and setting the quoted_identified off
eliminates the need for preserveSingleQuote
which would throw an error if any fields have
embedded single quotes*/
#artistsTmpTbl#
#artTmpTbl#
select *
from @tblArtists as a
left outer join @tblArt as b
on a.artistid = b.artistid
SET QUOTED_IDENTIFIER on
/* since my sql server setting is "on",
i set the quoted_identifier option back to on */
</cfquery>
<!--- output the results --->
<table>
<tr valign="top">
<td><cfdump var="#artists#" label="artists including artistid 16"></td>
<td><cfdump var="#art#" label="artists query query missing artistid 16"></td>
<td><cfdump var="#outerJoinQofQ#" label="qOfQ with left outer join"></td>
</tr>
</table>
