Overview of the Problem
“Query of Queries” (QoQ) is a ColdFusion/CFML feature that lets you run SQL-like statements over an in-memory query object (a dataset already returned by a real database query or constructed with queryNew/queryAddRow). QoQ is extremely useful for filtering, sorting, grouping, and aggregating data without hitting the database again. However, it’s not a full SQL engine. It implements a small subset of SQL-92, with limited functions and strict type rules. These constraints commonly produce “Query of Queries runtime error” messages such as SQL parse errors, unknown functions, column not found, or datatype conversion problems.
Understanding why QoQ errors happen—and how to fix them—requires knowing the feature’s limitations and the differences between QoQ SQL and your database’s SQL dialect.
Possible Causes
Syntax and Feature Limitations
- Only a subset of SQL is supported. Typically:
- Supported: SELECT, DISTINCT, FROM, WHERE, GROUP BY, ORDER BY, aggregate functions (COUNT, SUM, MIN, MAX, AVG), operators (IN, BETWEEN, LIKE).
- Usually not supported: JOINs, subqueries, UNION/UNION ALL, HAVING, database-specific functions (e.g., NOW(), DATEADD(), COALESCE()).
- Some Syntax that works in your RDBMS will fail in QoQ.
Datatype Mismatches
- QoQ is strict with datatypes. Common issues:
- Comparing strings to numbers (“10” vs 10).
- Sorting numeric values stored as strings (lexicographic vs numeric order).
- Comparing date/time columns to string literals.
- Boolean true/false vs numeric 1/0.
Column Names, Aliases, and Reserved Words
- Column not found due to:
- Typos, whitespace, or unexpected casing.
- Aliases referenced incorrectly in WHERE/GROUP BY/ORDER BY.
- Columns named with reserved words (e.g., “count,” “date”) without aliasing.
Date and Time Handling
- QoQ does not automatically parse date strings.
- Comparing dates requires the column to be typed as a date/timestamp and the Comparison value to be typed accordingly.
- Incorrect formatting or untyped dates cause conversion errors.
NULLs, Empty Strings, and Missing Values
- QoQ behavior around NULL vs empty string varies by engine/version.
- Queries created with queryNew may have empty strings unless explicitly set to NULL or typed columns.
Grouping and Aggregation Rules
- Every non-aggregated column in the SELECT list must appear in GROUP BY.
- HAVING is usually not supported; filter with WHERE or a second QoQ instead.
Scope and Availability
- The source query variable isn’t in scope or isn’t initialized when the QoQ runs.
- Using the wrong query name in FROM causes “table not found” or similar errors.
Performance and Size
- QoQ runs in memory. Very large datasets can cause timeouts or memory pressure.
- Avoid full scans and heavy aggregations on large result sets; pre-filter in the database when possible.
Cause / Solution Quick reference
- Unsupported JOIN/subquery → Flatten/merge data first; use multiple QoQs or CFML loops.
- Unknown function NOW()/COALESCE() → Replace with supported aggregates or pre-compute in DB or CFML; avoid DB-specific functions.
- Column not found → Verify ColumnList and exact names; alias safely and reference correctly.
- Type mismatch (string vs number/date) → Cast in the database, or coerce types in CFML before running QoQ.
- Wrong ORDER BY on string numbers → Convert to numeric column before ordering.
- Date comparisons failing → Ensure typed date columns and use cfqueryparam with cfsqltype, or pre-convert to date/timestamp.
- GROUP BY errors → Include all non-aggregated columns in GROUP BY.
Step-by-Step Troubleshooting Guide
Step 1: Capture the Exact Error and SQL
- Note the full error message and the SQL string sent to QoQ.
- If needed, log or dump the SQL right before execution:
<cfset writeLog(file=”qoq”, text=”QoQ SQL: #replace(sqlText, chr(10), ‘ ‘, ‘all’)#”)>
- Common message patterns:
- “Query Of Queries runtime error.”
- “Invalid SQL in Query of Queries.”
- “Unknown function …”
- “Column not found …”
- “Error casting value to …”
Step 2: Confirm You’re in QoQ Context
- QoQ requires dbtype=”query” and FROM must reference a query variable, not a table:
SELECT id, first_name, last_name
FROM qUsers
WHERE active = 1
ORDER BY last_name
- If you omit dbtype=”query”, ColdFusion will try to send the SQL to your DSN and fail because qUsers is not a real table.
Step 3: Inspect the Source Query’s Columns and Types
- Dump or log meta info to see the exact column names and types:
- Use the exact names reported. If you see unexpected casing or spaces, alias them in the original DB query:
SELECT id
, first_name AS firstName
, last_name AS lastName
, CAST(created_at AS TIMESTAMP) AS createdAt
, CAST(active AS INTEGER) AS isActive
FROM users
Step 4: Validate Your SQL Against QoQ Grammar
- Remove unsupported constructs:
- No JOINs, no subqueries, no UNION, typically no HAVING.
- Stick to WHERE, GROUP BY, ORDER BY, and aggregates.
- If you need a join, pre-join in the database or “join” in CFML by looping or merging queries.
Example: Simulate a lookup with IN (supported):
SELECT DISTINCT user_id
FROM user_roles
WHERE role = ‘admin’
SELECT *
FROM qUsers
WHERE id IN ( SELECT user_id FROM qIds )
If subqueries are not supported in your QoQ implementation, build a list:
SELECT *
FROM qUsers
WHERE id IN (#idList#)
Be mindful of quoting if id is string vs numeric. For strings, wrap each in quotes before joining.
Step 5: Fix Datatype Mismatches
- Numbers: Ensure numeric columns are numeric, not strings.
SELECT CAST(qty AS INTEGER) AS qty
- Alternatively, coerce in-memory when you build via queryNew:
<cfset q = queryNew(“id,qty”, “integer,integer”)>
<cfset queryAddRow(q)>
<cfset querySetCell(q, “id”, 1)>
<cfset querySetCell(q, “qty”, 10)>
- Booleans: Normalize to 1/0 or true/false consistently and compare accordingly.
Step 6: Handle Dates Properly
- Best: return properly typed date/timestamp from the database and compare using cfqueryparam in QoQ:
SELECT id, createdAt
FROM qUsers
WHERE createdAt >=
- If building the query in memory, define the column type:
<cfset q = queryNew(“id,createdAt”, “integer,timestamp”)>
- Avoid comparing typed date columns to plain strings (e.g., ‘2024-01-01’).
Step 7: Clean Up Column Names and Aliases
- If you used reserved words or awkward names, alias them in the original query or in a prior QoQ:
SELECT [count] AS itemCount, [date] AS orderDate
FROM qRaw
Note: Bracketed identifiers may not be supported in all QoQ engines. Safer: alias in the database query first, then reference the alias in QoQ:
SELECT count(*) AS itemCount, created_at AS orderDate
Step 8: Respect Grouping and Aggregation Rules
- Every column in SELECT that is not aggregated must appear in GROUP BY:
SELECT department, COUNT(*) AS total
FROM qUsers
GROUP BY department
ORDER BY total DESC
- If you need post-aggregate filters, run a second QoQ:
SELECT *
FROM qAgg
WHERE total > 10
Step 9: Watch Ordering and Aliases
- If ORDER BY alias fails, order by column position as a workaround:
SELECT department, COUNT(*) AS total
FROM qUsers
GROUP BY department
ORDER BY 2 DESC
- For numeric sorts on string columns, convert to numeric before QoQ.
Step 10: Retest, Log, and Iterate
- After each change, retest and log the SQL to verify.
- Keep examples of working QoQs as templates.
Examples: Working QoQ Patterns
Filter and Sort
SELECT id, firstName, lastName
FROM qUsers
WHERE isActive = 1
ORDER BY lastName, firstName
Aggregate with Grouping
SELECT department, COUNT(*) AS total, MIN(createdAt) AS firstJoin
FROM qUsers
GROUP BY department
ORDER BY total DESC
Pagination (LIMIT/OFFSET alternative)
QoQ often lacks LIMIT/OFFSET. Implement pagination in CFML:
SELECT id, lastName, firstName
FROM qUsers
ORDER BY lastName, firstName
Common mistakes and How to Avoid Them
-
Using JOIN or subqueries in QoQ
- Avoidance: Join in the database or combine datasets in CFML, then run QoQ over the merged result.
-
Calling database-specific functions (NOW(), COALESCE(), NVL(), DATEDIFF)
- Avoidance: Compute such values in the original DB query or in CFML. Use only QoQ-supported aggregates.
-
Forgetting dbtype=”query”
- Avoidance: Always include dbtype=”query” for QoQ blocks.
-
Referencing wrong column names or aliases
- Avoidance: Check q.ColumnList and metadata; standardize aliases in the original query.
-
Comparing typed dates to strings
- Avoidance: Use cfqueryparam with cfsqltype or ensure both sides are date/timestamp values.
-
Sorting numbers stored as strings
- Avoidance: Cast to numeric in the source query or rebuild the column with numeric type.
-
GROUP BY errors
- Avoidance: Include all non-aggregated columns in GROUP BY. Filter aggregated results with a second QoQ.
Best practices / Prevention Tips
-
Pre-filter in the database
- Reduce rows and columns before bringing results into memory.
-
Normalize types early
- Cast key fields (dates, numerics, booleans) in the database. If constructing in memory, define column types with queryNew’s second argument.
-
Keep column names simple
- Lowercase/underscore without spaces; alias away from reserved words.
-
Limit QoQ complexity
- Use QoQ for filtering/sorting/grouping; not for joins, unions, or advanced SQL.
-
Log and unit test
- Capture QoQ statements and write small tests for non-trivial filters.
-
Size awareness
- Avoid QoQ over hundreds of thousands of rows. Consider re-querying the database or caching pre-aggregated views.
-
Version notes
Key Takeaways
- Query of Queries is a lightweight, in-memory SQL subset; do not expect full RDBMS Features.
- Most QoQ errors arise from unsupported syntax, datatype mismatches, or incorrect column references.
- Fixes typically involve simplifying SQL, standardizing datatypes, aliasing columns, and splitting logic into multiple QoQs.
- For dates and numerics, ensure proper typing via DB casts or queryNew column definitions; cfqueryparam can help in QoQ comparisons.
- Prevent problems by pre-filtering in the database, keeping QoQ small and focused, and validating column metadata before writing QoQs.
FAQ
Can I perform JOINs in Query of Queries?
Usually no. QoQ generally doesn’t support JOIN or multiple tables in FROM. Join in the database, or merge datasets in CFML (e.g., building lookup maps by ID), then query the merged result with QoQ.
How do I compare dates safely in QoQ?
Ensure the date column is typed as date/timestamp and use a typed Comparison value. Prefer cfsqltype with cfqueryparam inside QoQ:
WHERE createdAt >=
Alternatively, cast in the database before bringing data into QoQ.
Why does ORDER BY sort “10” before “2”?
Your column is likely a string. Convert it to a numeric type in the source DB query (CAST) or define it as integer when building the query in memory. Then ORDER BY will be numeric.
What functions are supported in QoQ?
QoQ reliably supports aggregate functions (COUNT, SUM, MIN, MAX, AVG) and basic predicates (IN, BETWEEN, LIKE). Database-specific functions (NOW, COALESCE, DATEDIFF) are not supported. Compute those values in the DB or in CFML before running QoQ.
