Query Of Query of ordered result set error in CFSCRIPT in CF10

Question

I have come across a very interesting error when doing a query of a query inside a CFSCRIPT tag...

I get a result set back from a SQL Server stored procedure. It is a list of metric names (METRIC_ID) and the actual metric measurements.

I need to pull the METRIC_ID from this result set to do a JOIN on another query that has display and other formatting attributes for display in a web page.

The following code executes with no errors when the query is unordered:

var displayDataQuery = new Query();

displayDataQuery.setName("joinedQuery");

displayDataQuery.setDBType("query");

displayDataQuery.setAttributes(qry1 = fullMetricsListQuery);

displayDataQuery.setAttributes(qry2 = arguments.cubeData);

displayDataQuery.setSQL("SELECT METRIC_ID FROM qry2");

var tempResultSet = displayDataQuery.execute();

var cubeMetricIDList = tempResultSet.getResult();

When I take the same exact query results from the same stored procedure, but it's ordered by an ORDER BY clause, this statement;

var tempResultSet = displayDataQuery.execute();

throws the following error - "The value '' cannot be converted to a number." That's 2 single quotes, signifying an empty string between "value" and "cannot."

Wait, what??? I just want a list of metric names, varchars. Why is there a conversion error...?

The error ultimately points back to ColdFusion10\cfusion\CustomTags\com\adobe\coldfusion\base.cfc, line 445.

OK, this is where this takes an interesting turn...

Starting at line 444 in base.cfc, Line 445 bolded in red:

<cfloop index="i" from="2" to="#ArrayLen(sqlArray)#">

</cfif>

#getPreserveSingleQuotes(sqlArray)#

</cfloop>

</cfif>

So if I understand this correctly, when I have an unordered query, this code in base.cfc reads the SQL set in displayDataQuery.setSQL("SELECT METRIC_ID FROM qry2"), but doesn't when the query is ordered by METRIC_ID...?

Ah, wait... the CFDUMP when using an ordered data set says something else...

query
RESULTSET
CACHED	false
EXECUTIONTIME	4
SQL	SELECT * FROM qry1 ORDER BY METRIC_ID, LOCATION

I'm pretty much stumped why the same data in unordered set works while in an ordered set causes this error.

Any and all helpful suggestions or clues will be greatly appreciated!!!

Golden_Jet · Accepted Answer

Golden Jet wrote:This code threw the exact same error at the beginning of the CFQUERY: SELECT METRIC_ID FROM arguments.cubeData Do you mean the error occurred without the order-by clause?Do you mean the error occurred without the order-by clause?Since the error doesn't occur with an unordered data set, I'm a little confused why you would ask this...?I followed your suggestion:Run the same code, but then use the cfquery tag instead of cfscript. Does the error still occur?My assumption was your suggestion was to test against an ordered data set as that's what was causing the original error. Unfortunately the error occurs in both tag-based and script-based scenarios. This makes absolutely no sense why this occurs. The order of a data set should have no bearing on a Query of a Query on a column defined as a varchar. If there are null values in the other columns defined as some type of numeric, big deal, the code in question isn't trying to evaluate those columns. Even more interesting is why unordered vs. ordered makes a difference.

BKBK · Answer

The explanation got a bit confusing towards the end. You first said the query that caused the issue is "SELECT METRIC_ID FROM qry2 ORDER BY METRIC_ID". Later on you mention the query "SELECT * FROM qry1 ORDER BY METRIC_ID, LOCATION". Which one is it?

Run the same code, but then use the cfquery tag instead of cfscript. Does the error still occur?

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded