• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

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

Explorer ,
Jul 08, 2014 Jul 08, 2014

Copy link to clipboard

Copied

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:

<cfif sqlType neq "" and arraylen(sqlParams) gt 0>

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

          <cfif (i-1) lte arraylen(sqlParams)>

               <cfqueryparam attributeCollection="#sqlParams[i-1]#"/>

          </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
CACHEDfalse
EXECUTIONTIME4
SQLSELECT * 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!!!

Views

518

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Explorer , Jul 10, 2014 Jul 10, 2014

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 mak

...

Votes

Translate

Translate
Community Expert ,
Jul 09, 2014 Jul 09, 2014

Copy link to clipboard

Copied

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jul 09, 2014 Jul 09, 2014

Copy link to clipboard

Copied

Oops, you caught a defect in my post!

The SQL should be "SELECT METRIC_ID FROM qry2 ORDER BY METRIC_ID" in both places.  I copied/pasted the wrong CFDUMP output.

The entire CFC is in a CFSCRIPT tag, but I'll do a little rework and see if your suggestion works.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jul 09, 2014 Jul 09, 2014

Copy link to clipboard

Copied

This code threw the exact same error at the beginning of the CFQUERY:

<cfcomponent>

  <cffunction name="getDisplayDataValueList"

  returntype="any"

         access="public"

  displayname="getDisplayDataValueList"

  description="I return a value list of the metric ids to be displayed">

  <cfargument name="cubeData" type="query" required="true">

  <cfquery name="cubeMetricIDList" dbtype="query" >

  SELECT METRIC_ID

   FROM arguments.cubeData

  </cfquery>

  <cfreturn ValueList(cubeMetricIDList.metric_id)>

  </cffunction>

</cfcomponent>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jul 09, 2014 Jul 09, 2014

Copy link to clipboard

Copied

Golden Jet wrote:

This code threw the exact same error at the beginning of the CFQUERY:

  <cfquery name="cubeMetricIDList" dbtype="query" >

  SELECT METRIC_ID

   FROM arguments.cubeData

  </cfquery>

Do you mean the error occurred without the order-by clause?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jul 10, 2014 Jul 10, 2014

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jul 10, 2014 Jul 10, 2014

Copy link to clipboard

Copied

LATEST

My suggestion was indeed to test against an ordered data set, as that is what was causing the original error. However, the query you apparently used - which I quoted - is unordered.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation