> <cfquery name="getnews" datasource="#request.MainDSN#" debug>
> SELECT n.createdby, n.id, n.category, n.createdon, n.killdate, n.body,
> n.viewby
> FROM news n
> WHERE n.category = 3 AND
> n.killdate >= #attributes.currenttime# AND (
> <cfloop list="#session.curruser.offices.list#" index="e">
> #ListContains(n.viewby, e)# <> 0 OR
> </cfloop>
> n.id = 1 )
> </cfquery>
OK, first of all you've got to realise how CF talks to SQL
It builds the whole SQL Query, then sends it to the SQL Engine... Since
SQL doesn't run ColdFusion, you can't use CF tags within SQL statements
You'd have to deal with it as a list of ORs;
<cfloop list="#session.curruser.offices.list#" index="e">
',' + n.viewby + ',' like '%,#e#,%' OR
</cfloop>
Now, the reason I put the ',' + n.viewby + ',' rather than just n.viewby
is because it probably doesn't have commas on either end, so you've got
to check for these
And you can't do
N.viewby like '#e#'
As if e is 1, then 1, 10, 11, 12, 21, etc. etc. are all like it...
Try to Normalize though, it'll save you heart-ache in the future
Philip Arnold
Technical Director
Certified ColdFusion Developer
ASP Multimedia Limited
Switchboard: +44 (0)20 8680 8099
Fax: +44 (0)20 8686 7911
www.aspmedia.co.uk
www.aspevents.net
An ISO9001 registered company.
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**********************************************************************
> -----Original Message-----
> From: John Kivus [mailto:[EMAIL PROTECTED]]
> Sent: 10 May 2002 03:37
> To: CF-Talk
> Subject: Looping in a Query
>
>
> I'm having a mental block trying to write a query. I have a list of
> numbers (which are in no particular order) and I need to see
> if just one
> of those numbers is in a field in the database which also has
> a list of
> numbers. I figured I'd use a <cfloop> inside my query by I'm having a
> little trouble. When I try
>
>
>
> it cannot resolve "n.viewby". Am I really missing the boat on
> this one?
> Or I do I have a minor mistake? For some reason I think there
> just must
> be a better way.
>
> Sincerely,
> John Kivus
>
> FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
> Archives: http://www.mail-archive.com/[email protected]/
> Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
>
>
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
