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.

Answer 1

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.