Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to loop a query and update the where clause.

Posted on 2014-03-12
15
Medium Priority
?
346 Views
Last Modified: 2014-03-14
I will do my best to explain this. I need to loop over a query similar to the one below and replace the "Arguments.CarName" in the Where Clause each time it loops with a list of CarNames. The List of CarNames would be a "distinct" list from that same table. could someone please assist? I am using CF10. Thank you.

SELECT COUNT(CarNumber) 
	FROM  dbo.tblCars
	WHERE IncidentDate BETWEEN <cfqueryparam value="#arguments.IncidentDate1#" cfsqltype="cf_sql_date"> AND <cfqueryparam value="#dateAdd('d', 1, arguments.IncidentDate2)#" cfsqltype="cf_sql_date"> )
	and CarName = <cfqueryparam value="#arguments.CarName#" cfsqltype="cf_sql_varchar">

Open in new window

0
Comment
Question by:earwig75
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 39924626
Running queries w/in a loop should be avoided if possible. Do you really need to loop? Why not just feed in a list of car names and get the counts for all in single query, ie:

CarName  | NumOfCars
Car AAAA | 22
Car BBB    | 43
Car BBB    | 32
....

Something like ...

SELECT CarName, COUNT(CarNumber)  AS NumOfCars
FROM   dbo.tblCars
WHERE IncidentDate BETWEEN {date1} AND {date2}
AND      CarName IN
(
<cfqueryparam value="#ListOfNames#" list="true" cfsqltype="cf_sql_varchar">
)
0
 

Author Comment

by:earwig75
ID: 39924649
I displayed a very abbreviated example but that may work. How would I build the "ListOfNames"? I haven't done this type of ColdFusion coding in quite a while.

Thank you.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39924664
Depends. What's the source of the car names? If it's from a query, just use:

<cfset listOfNames = ValueList(yourQueryName.columnName)>
... feed into other query ...
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:earwig75
ID: 39924741
I'm sorry. the example I gave won't work with that solution and it is probably my fault. I was hoping a loop would work. My query looks something like this. I am trying to get the list of values for each car name. It's a query with subqueries.
<cfquery .....>
SELECT 
  (SELECT COUNT(CarNumber) 
	FROM  tblCars
	WHERE (tblCars.Created BETWEEN '1/1/2014' AND '3/1/14' )
	
		   AND      CarName IN  (<cfqueryparam value="#ListOfNames#" list="true" cfsqltype="cf_sql_varchar">)
 ) as CreatedCars, 



  (SELECT COUNT(CarNumber)
    FROM  tblCars
    WHERE (tblCars.Created BETWEEN '1/1/2014' AND '3/1/14') 
	AND	(tblCars.Status = 'Closed')
	AND (ChangePerson NOT IN ('blah', 'blah2'))
   
		   AND      CarName IN  (<cfqueryparam value="#ListOfNames#" list="true" cfsqltype="cf_sql_varchar">)
) as ClosedCars, 
				
  (SELECT COUNT(tblCars.CarNumber) 
	FROM   tblCars INNER JOIN
                      tblCarsIncidents ON tblCars.ID2 = tblCarsIncidents.ID2
	WHERE (tblCars.Created BETWEEN '1/1/2014' AND '3/1/14') 
	 AND (tblCars.Status = 'Closed') AND (tblCarsIncidents = 'Red') AND tblCarsIncidents.Status = 'Closed'
		
		   AND      CarName IN  (<cfqueryparam value="#ListOfNames#" list="true" cfsqltype="cf_sql_varchar">)
     ) as ClosedCarsRed
</cfquery>

Open in new window

0
 

Author Comment

by:earwig75
ID: 39924761
So, I'd like to output the query and show a list of Carnames and then numbers for each like this:

CarName     CreatedCars     Closed Cars     ClosedCarsRed
--------------------------------------------------------------------------
CoolCar              5                       7                       4
NeatCar              4                       4                       8
WowCar              4                      8                       10
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39924849
Ooops, our posts clashed.  I think I understand now.  Let me look over the subqueries..
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39924889
(Edit fix syntax error).

Looks like all of the subqueries are filtered on the same createdDate range. Try this smaller query first (not tested).  Does it return the right numbers? Then we can add in the incidents table subquery.

SELECT c.CarName
      , COUNT(c.CarNumber) AS CreatedCars
      , SUM(CASE
                WHEN c.Status = 'Closed'  AND c.ChangePerson NOT IN ('blah', 'blah2')  THEN 1
                ELSE 0
              END
        ) as ClosedCars
FROM   tblCars c
WHERE  c.Created BETWEEN '1/1/2014' AND '3/1/14'
GROUP BY c.CarName
0
 

Author Comment

by:earwig75
ID: 39925076
In my example I only used 3 total queries but I have about ten and most of them have joins like the one you omitted.
I can't test until tomorrow but that looks good so far.
Thank you.
0
 

Author Comment

by:earwig75
ID: 39925107
Could I put my original query in a CFC and then send the carname as in invoke argument in a loop somehow?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39925131
Yeah, you can put it in a CFC, but so far, I don't think you need a loop.  If you simply filter the outer query on the car names you want , ie "CoolCar,NeatCar,WowCar" .... the results will contain the totals for each of those car names:

<!--- obviously use cfqueryparam in the real query --->
SELECT c.CarName
      , COUNT(c.CarNumber) AS CreatedCars
      , SUM(CASE
                WHEN c.Status = 'Closed'  AND c.ChangePerson NOT IN ('blah', 'blah2')  THEN 1
                ELSE 0
              END
        ) as ClosedCars
FROM   tblCars c
WHERE  c.Created BETWEEN '1/1/2014' AND '3/1/14'
AND       c.CarName IN ( #ListOfCarNamesYouWant# )  <=== add list as a filter
GROUP BY c.CarName
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39925292
For tomorrow, assuming the base query works, something like this should do it for the subquery. Note, I think the earlier example omitted the name of the color column, so be sure to update it in the query:

SELECT c.CarName
      , COUNT(c.CarNumber) AS CreatedCars
      , SUM(CASE
                WHEN c.Status = 'Closed'  AND c.ChangePerson NOT IN ('blah', 'blah2')  THEN 1
                ELSE 0
              END
        ) AS ClosedCars
      , (
         SELECT COUNT(*)
           FROM    tblCarsIncidents i
           WHERE  i.ID2 = c.ID2
           AND      i.Status = 'Closed'
           AND      i.Status = c.Status
           AND      i.MissingColumnName = 'Red'
      )
        AS ClosedCarsRed
FROM   tblCars c
WHERE  c.Created BETWEEN '1/1/2014' AND '3/1/14'
AND       c.CarName IN
(
<cfqueryparam value="#ListOfCarNamesYouWant#" list="true"
                cfsqltype="(your type here)...">
)
GROUP BY c.CarName
0
 

Author Comment

by:earwig75
ID: 39925469
I have one more obstacle with this method. When I output  the query for each row I need to add columns for each CarName that will have some calculations of the other columns. Will that be possible?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39926385
Probably.  All depends on what the calculations are ... if it's some sort of summary of the counts for example, you might just want to do that in CF code.  ie

CarName     CreatedCars     Closed Cars     ClosedCarsRed            Total
------------------------------------------------------------------------------------
CoolCar              5                       7                       4                     16
NeatCar              4                       4                       8                     16
WowCar              4                      8                       10                    22
0
 

Author Comment

by:earwig75
ID: 39926446
I am putting percentages and other calculations next to the other numbers. for example:

CarName     CreatedCars     Closed Cars     ClosedCarsRed            Total
------------------------------------------------------------------------------------
CoolCar              10                       7                       7   (75%)             24
NeatCar              4                       4                       8                          16
WowCar              4                      8                       10                         22

I think I have it working by looping over the cfinvoke. Using your method, I'm not sure how I would add other hard-coded columns and other numbers in the existing columns since the rest of the output is coming from the query alone. You answer my original question; I should have provided much more detail.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 39926582
(Edit) Well, since you need to aggregate twice: total per car name, total overall you cannot do it in one step. First you have to get the aggregate totals per car. Then calculate the overall totals.

Normally I'd suggest doing that in SQL, using a derived table, like this:

SELECT   t.CarName
              , t.CreatedCars
              , t.ClosedCars
              , t.ClosedCarsRed
              , (t.CreatedCars + t.ClosedCars + t.ClosedCarsRed ) AS TotalCars
FROM   (
                    SELECT  CarName, CreatedCars, ClosedCars, ClosedCarsRed
                    FROM     Table
                    WHERE ....
            )  AS t

Then using the "Total" column to generate percentages in your output loop

<cfoutput query="queryName">
       #ClosedCarsRed#  ( #ClosedCarsRed/Total * 100)# % )
</cfoutput>

But given the complexity of your base query that could get a little ugly.  You might consider using temp tables. Insert the numbers into a temp table. Then do a self join to return the data and totals in a single resultset.

Another option is to run the query as usual. Then add a "Total" column to it:

        <cfset queryAddColumn(yourQuery, "Total", [])>

Then loop through it to update the "Total" value for each row.  

        <cfloop query="yourQuery">
              <!--- calculate total cars in all relevant columns --->
              <!--- where columnNamesYouWantToTotal = "CreatedCars,ClosedCars,..." --->
              <cfset total = 0>
              <cfloop list="#columnNamesYouWantToTotal#" index="colName">
                       <cfset total += val(yourQuery[colName][currentRow])>
              </cfloop>
              <!--- store total --->
              <cfset yourQuery["Total"][currentRow] = total>
        </cfloop>

Then use the "Total" to calculate the percentages as described above.
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question