You could simplify it by doing this:
<cfquery name="MovementClasses" datasource="#dsn#">
SELECT MIN(CalendarDate) as MinDate, MAX(CalendarDate) as
MaxDate, EventName, GroupID
FROM Calendar
WHERE GroupID IN (
<cfqueryparam cfsqltype="cf_sql_varchar"
value="#ValueList(GroupOne.GroupID,',')#" list="true"/>
)
GROUP BY EventName, GroupID
</cfquery>
On 9/19/05, sp @ who. net sp @ who. net <[EMAIL PROTECTED]> wrote:
> I am having difficulties outputting a ListQualify in a query.
>
> Here is the query
>
> <cfset TodayDate = DateFormat(Now(), "mm/dd/yyyy")>
>
> <!--- get the groupid of movement classes --->
> <cfquery name="GroupOne" datasource="#dsn#">
> SELECT Distinct(GroupID), EventName
> FROM Calendar
> WHERE EventType = 'Movement Class'
> and CalendarDate > '#TodayDate#'
> GROUP BY GroupID, Eventname
> ORDER BY EventName ASC
> </cfquery>
>
> <!--- set GroupID to comma delimited list --->
> <cfset MovementList = ValueList(GroupOne.GroupID,',')>
>
> <!--- qualify the list with single quotes --->
> <cfset MovementList = ListQualify(MovementList," ' ", ",","ALL")>
>
>
> The output
> <cfoutput>#MovementList#</cfoutput> gives the following
>
> '6FA07A77-3048-2A59-E85C85CF523051C6','6FA24EAB-3048-2A59-E83D53893307A2BB'
>
>
> BUT when i put the MovementList in the following query
>
> <cfquery name="MovementClasses" datasource="#dsn#">
> SELECT MIN(CalendarDate) as MinDate, MAX(CalendarDate) as
> MaxDate, EventName, GroupID
> FROM Calendar
> WHERE GroupID IN (#MovementList#)
> GROUP BY EventName, GroupID
> </cfquery>
>
> I get the following error. For some reason the list becomes qualified with
> double quotes. Here is the error
>
> Error Executing Database Query.
> [Macromedia][SQLServer JDBC Driver][SQLServer]Line 4: Incorrect syntax near
> '6'.
>
> 25 : MaxDate, EventName, GroupID
> 26 : FROM Calendar
> 27 : WHERE GroupID IN (#MovementList#)
> 28 : GROUP BY EventName, GroupID
> 29 : </cfquery>
>
> SQL SELECT MIN(CalendarDate) as MinDate, MAX(CalendarDate) as MaxDate,
> EventName, GroupID FROM Calendar WHERE GroupID IN
> (''6FA07A77-3048-2A59-E85C85CF523051C6'',''6FA24EAB-3048-2A59-E83D53893307A2BB'')
> GROUP BY EventName, GroupID
>
> How do I correct this?
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:218669
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
