0 Replies Latest reply on Jul 9, 2013 10:43 AM by jfb00

    cfthread with query

    jfb00 Level 3

      Hi All,

      Here is my query:

       

      <cfquery name="qryData"  datasource="#application.DBconn#" >

              select columnA, columnB

              <cfloop from="1" to="5" index="y">

                  ,SUM (Case when wType='FULL' then YEAR_#y# else 0 end) as FULL_#y#

                  ,SUM (Case when wType='PART' then YEAR_#y# else 0 end) as PART_#y#

               </cfloop>

              from (

                select columnA, columnB, wType

                <cfloop from="1" to="5" index="y">

                     ,SUM(case when fy = #y# then totalDI else 0 end) as YEAR_#y#

                </cfloop>

                from tableXX

                union all

                select columnA, columnB, wType

                <cfloop from="1" to="5" index="y">

                     ,SUM(case when fy = #y# then totalCE else 0 end) as YEAR_#y#

                </cfloop>

                from viewYY

                )

              group by columnA, columnB, wType

              order by columnB

      </cfquery>

       

      How can i use cfthread to speed up the query?

       

      I try:

      <cfthread name="threadA" action="run">

      <cfquery name="variables.qryA"  datasource="#application.DBconn#" >

           select columnA, columnB, wType

                <cfloop from="1" to="5" index="y">

                     ,SUM(case when fy = #y# then totalDI else 0 end) as YEAR_#y#

                </cfloop>

                from tableXX

      </cfquery>

      </cfthread>

       

      <cfthread name="threadB" action="run">

      <cfquery name="variables.qryB"  datasource="#application.DBconn#" >

           select columnA, columnB, wType

                <cfloop from="1" to="5" index="y">

                     ,SUM(case when fy = #y# then totalDI else 0 end) as YEAR_#y#

                </cfloop>

                from viewYY

      </cfquery>

      </cfthread>

       

      <cfthread name="threadA, threadB"  action="join"  />

       

      <cfquery name="qryData"  dbType="query" >

              select columnA, columnB

              <cfloop from="1" to="5" index="y">

                  ,SUM (Case when wType='FULL' then YEAR_#y# else 0 end) as FULL_#y#

                  ,SUM (Case when wType='PART' then YEAR_#y# else 0 end) as PART_#y#

               </cfloop>

              from (

                select * from variables.qryA

                union all

               select * from variables.qryB

               )

              group by columnA, columnB, wType

              order by columnB

      </cfquery>

       

      I got an error because i cannot use CASE in a Q of Q.

      Maybe it is a way to use the cfthread inside my first query.

      Any ideas?

      Thanks!