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: ad...@acfug.org [mailto:ad...@acfug.org] On Behalf Of John
Youngman
Sent: Tuesday, February 15, 2011 3:46 PM
To: discussion@acfug.org
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
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 <http://www.fusionlink.com>
-------------------------------------------------------------
-------------------------------------------------------------
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 http://www.fusionlink.com
-------------------------------------------------------------
