Programming ColdFusion

CFSQL

Now that you have an understanding of advanced SQL, it’s time to introduce a new concept known as “query of a query” or CFSQL (ColdFusion SQL). Introduced in ColdFusion 5.0, CFSQL allows you to use the CFQUERY tag to query an already existing query using a subset of ANSI SQL 92. This feature makes it easy to program functionality previously difficult or impossible to implement in ColdFusion. Some potential uses for CFSQL include:

• Manipulate query objects (sort, summarize, group, etc.) returned by other ColdFusion tags such as CFHTTP, CFFTP, CFLDAP, CFPOP, CFSEARCH, or CFSTROEDPROC as well as the various query functions

• Perform joins and unions between tables from different data sources

• Resorting a query result set without having to go back to the data source

• Moving entire tables into memory (Macromedia recommends no more than 10,000 rows), effectively speeding up query times because ColdFusion no longer has to make a round trip to the database for each query performed

Regardless of what you decide to use CFSQL for, you need to know what SQL constructs are available. For starters, you can perform only an SQL SELECT in CFSQL. This means that CFSQL can be used only to select records, not INSERT, UPDATE, or DELETE them. This makes sense, as the purpose of CFSQL is to allow you to perform a query against an already existing query. When you perform your SELECT, a number of SQL keywords are available to help you construct your query. These keywords are FROM, WHERE, ORDER BY,