macromedia.coldfusion.cfml_general_discussion
[Top] [All Lists]

Re: using a list in the where part of my query

Subject: Re: using a list in the where part of my query
From: "cf_dev2"
Date: Thu, 16 Aug 2007 19:38:43 +0000 UTC
Newsgroups: macromedia.coldfusion.cfml_general_discussion
You don't need a cfloop.  Try an IN(...) clause instead.  Obviously you have to 
validate that the #riderlist# is not empty first or the query will error.

 SELECT riderId,
 SUM(IIf(month(rideDate)=1, rideDistance, 0)) AS janSum,
 SUM(IIf(month(rideDate)=2, rideDistance, 0)) AS febSum,
 SUM(IIf(month(rideDate)=3, rideDistance, 0)) AS marSum,
 SUM(IIf(month(rideDate)=4, rideDistance, 0)) AS aprSum,
 SUM(IIf(month(rideDate)=5, rideDistance, 0)) AS maySum,
 SUM(IIf(month(rideDate)=6, rideDistance, 0)) AS junSum,
 SUM(IIf(month(rideDate)=7, rideDistance, 0)) AS julSum,
 SUM(IIf(month(rideDate)=8, rideDistance, 0)) AS augSum,
 SUM(IIf(month(rideDate)=9, rideDistance, 0)) AS sepSum,
 SUM(IIf(month(rideDate)=10, rideDistance, 0)) AS octSum,
 SUM(IIf(month(rideDate)=11, rideDistance, 0)) AS novSum,
 SUM(IIf(month(rideDate)=12, rideDistance, 0)) AS decSum
 FROM mileageLog
 WHERE riderId IN (<cfqueryparam value="#riderList#" cfsqltype="the column type 
here" list="true"> )
 AND Year(rideDate)=#useYear#
 GROUP BY riderId
 ORDER BY riderId



<Prev in Thread] Current Thread [Next in Thread>