Perhaps in the Approach #2 you need "ticks" around the ids (single quotation marks)? if you are working in varchar, why is it erroring on a date type? __________________________ John Youngman j...@jg-technologies.net http://www.linkedin.com/in/johngyoungman
On Feb 15, 2011, at 3:40 PM, Matthew Nicholson wrote: > Afternoon All! > > I seem to be running into a bit of a syntax error for query within a query. > > Here’s the situation, > > I’ve got a query that creates contains an idea with an associated display > variable. I’d like to take that query and use QoQ limit the results based > upon a separate select statement or a list in a where clause. > > Approach 1: (Nested Select) > <cfquery dbtype="query" name="spListNarrow"> > select * > from query > where id in > ( > select distinct id > from table > ) > </cfquery> > > > Approach 2: (Create the List and try to use it) > <cfquery name="qryGetID" datasource="#main_dsn#" dbtype="ODBC"> > select distinct id from table > </cfquery> > > <CFSET IDs = ValueList(qryGetID.id, ", ")> > > <cfquery dbtype="query" name="spListNarrow"> > select * > from query > where id IN ( <cfqueryparam cfsqltype="cf_sql_varchar" value="#IDs#" > list="yes"> ) > </cfquery > > Approach 1 fails as it seems to ignore the results returned from the nested > select and Approach 2 has issues due to date types. > > Any thoughts or suggestions would be greatly appreciated, I feel like I’m > missing something quite simple in all of this. > > Thanks! > > Matthew R. Nicholson > To find what you seek in the road of life, the best proverb of all is that > which says: "Leave no stone unturned." > ~Edward Bulwer Lytton > > > ------------------------------------------------------------- > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?fa=login.edituserform > > For more info, see http://www.acfug.org/mailinglists > Archive @ http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by FusionLink > -------------------------------------------------------------
