Scott Stewart wrote: > i have a table with a field (study_category.category_date) that is a > comma delimited list (2004,2005,2006,2007) stored as a NVARCHAR > > I need to see if a passed value is in the list: > > WHERE (#arguments.cat_date# in (study_category.category_date)) > > arguments.cat_date is a four digit number representing the date. > > the where clause doesn't seem to work (returns no records, when > logically it should) > No it logically shouldn't. The in clause is to compare a single field to a list of values. It does not compare that a value is part of another value that happens to be a list.
With a denormalized data design such as this you are going to have to work hard using the LIKE clause. WHERE study_category.category_date LIKE '#arguments.cat_date#' Your lucky that a year value can not be part of a larger value or that where statement quickly becomes much more complex. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314560 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
