Issue with ColdFusion Query of Query after upgrading ColdFusion server from CF9 to CF21

Question

I cannot figure this out. My company recently upgraded our ColdFusion Server from CF9 to CF2021. One of my pages uses queries of queries to run. Prior to the upgrade the page worked fine. However after the upgrade the query of query where statement is not returning any results. Even though the original query has the data. The data is coming from DB2 data source in case that changed with the new version.

<cfquery name         = "query_data_sample"  dbtype="query">
   SELECT
            SAMPLE_DATE
       ,    SAMPLE_NBR
       ,    SAMPLE_MISC_NBR

       ,  SUM (RECD_QTY)      AS   TTL_RECD_QTY
       ,  SUM (MISC_QTY_1)    AS   TTL_MISC_QTY_1
       ,  SUM ( 1 )           AS   TTL_COUNT
   FROM    original_query_result
   WHERE    1=1
      AND SAMPLE_DATE = <cfqueryparam value = "#original_query_result.SAMPLE_DATE#" cfsqltype = "CF_SQL_DATE">
      AND SAMPLE_NBR  = <cfqueryparam value = "#trim(original_query_result.SAMPLE_NBR)#"  cfsqltype = "CF_SQL_CHAR">
      AND SAMPLE_MISC_NBR = <cfqueryparam value = "#trim(original_query_result.SAMPLE_MISC_NBR)#"   cfsqltype = "CF_SQL_CHAR">


   GROUP BY
        SAMPLE_DATE
   ,    SAMPLE_NBR 
   ,    SAMPLE_MISC_NBR 


 </cfquery>

When I comment out the AND statements and dump out the above query I can see the output of the original_query_result with all the TTL values and Sample rows. However if I want to grab just one row with the AND statements then the query comes back as not finding the row. I am doing a cfoutput query = original_query_result and trying to get the total quantity to display on each row in my output grid table.

Any help would be greatly appreciated. I have been trying to figure this out for more then a week now.

I would start by adding the WHERE filters one at a time, in order to identify which one is dropping the results. Then move on to figuring out why. Also check the bug tracker https://tracker.adobe.com/. Though the ideal solution would be to perform the aggregations in the db, instead of one at a time using a QoQ. — SOS, Mar 03 '22 at 22:16
Also, what are the datatypes of those 3 columns in the database, and are they different than the cfsqltypes? For example, are the string columns type `char` or `varchar`? — SOS, Mar 03 '22 at 23:23
I think your problem will get fixed if you try to play around with the `cfqueryparam` `cfsqltype` in your `AND` clauses. — akashb, Mar 04 '22 at 00:10
QofQ is case sensitive so what happens if you keep only where first and and remove the two char clauses? Also I'd try removing those queryparams to test - there's some date conversions between string and dates in QoQs. — Steve, Mar 04 '22 at 13:57

Jackie Daytona · Answer 1 · 2022-03-11T14:37:24.053

I saw something similar a while back and found this post:

https://web.archive.org/web/20180210052449/http://blog.dkferguson.com/index.cfm/2010/2/17/Using-Querie-of-Queries-with-CFDirectory

Basically I had to CAST the date and use cfqueryparam (as you are):

SAMPLE_DATE = CAST(<cfqueryparam value="#original_query_result.SAMPLE_DATE#" cfsqltype="CF_SQL_DATE"> AS date)

The post said they had to cast both sides but I didn't find this.

Issue with ColdFusion Query of Query after upgrading ColdFusion server from CF9 to CF21

1 Answers1