Hi,
I could use some sage advice. We have a project that needs to be in access
(do not ask or tell as to why) and have a condition where we need to
construct a dynamic query. What I am looking for is not necessarily the
best SQL construct but the one that is less intensive and therefore
troublesome to the Access ODBC connector.
Remember I am looking for the less troublesome query for the access ODBC
connector (and yes it MUST be access <sigh>). The variable Nodelist will
always return a list of 1 or more comma delimited numbers
Here is some stripped down code:
<cf_childnodes parentitemid="#Level#" Datasource="#App_db#">
<!--- returns comma delimited numeric list in variable 'nodelist' --->
Use this query?
<cfquery Name="qry1">
SELECT Item_ID,, Product_Name
FROM ProductSeries
WHERE (
<cfloop index="lvl" list="#nodeList#" delimiters=",">
(ItemID=#lvl#) OR
</cfloop>
(1=0))
ORDER BY Product_Name
</cfquery>
Or this query?
<cfquery Name="qry2">
SELECT Item_ID,, Product_Name
FROM ProductSeries
WHERE (ItemID IN (#nodelist#))
ORDER BY Product_Name
</cfquery>
Any and all recommendations are greatly appreciated (other than go with
SQL). I am trying to make this project scale to the limits of what Access
can handle (with all reads never a write) by constructing the queries that
will give the best performance and less trouble with CF and access's odbc
connector. Yes it's a NT4.0 server.
Thank you in advance.
Best regards,
Dennis Powers
UXB Internet - A Web Design and Hosting Company
tel: (203)879-2844 fax: (203)879-6254
http://www.uxbinternet.com/
http://dennis.uxb.net/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
