Awesome! __________________________ John Youngman [email protected] http://www.linkedin.com/in/johngyoungman
On Feb 15, 2011, at 3:59 PM, Matthew Nicholson wrote: > It would look as though I’ve been tinkering with this way too much today! > > Your question John got the juices flowing for sure. I had assumed that the > listed produced by ValueList couldn’t be converted to a list of Integers (So > I had kept cfsqltype="cf_sql_varchar" vs cfsqltype="cf_sql_integer") > > Once I ensured we were comparing apples to apples, approach 2 worked > swimmingly with the QoQ! > > 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 > > From: [email protected] [mailto:[email protected]] On Behalf Of John Youngman > Sent: Tuesday, February 15, 2011 3:46 PM > To: [email protected] > Subject: Re: [ACFUG Discuss] Query within a Query > > 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 > [email protected] > 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 > ------------------------------------------------------------- > > > ------------------------------------------------------------- > 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 > -------------------------------------------------------------
