Re: Query Too Complex for Access?
Have you tried: AND Works.ThisInventory not in (#listQualify(valueList(getActiveWorks.ThisReference),')#) Dave I went in and applied the solution to all my other queries where I was having a problem and it works great. You were right. None better. Thanks! Already I am learning a lot from this forum. And I am using cfqueryparam now too.. ~ David G. Moore, Jr. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311402 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Query Too Complex for Access?
I know I am setting myself up for another Query too complex issue, so before I start I thought I would ask for suggestions. I run into this when I have to reference two different Access databases that are Client imposed (don't ask). Basically, I have to use one for active data and one to show available date (minus the active data). This is, of coures, where the problem comes in. The queries will help: cfquery name=getActiveWorks datasource=#DSN# SELECT * FROM Works WHERE Works.PageReference = #FORM.ThisPage# AND Works.TypeReference = '#FORM.ThisType#' /cfquery cfquery name=getWorks datasource=#DSN2# SELECT * FROM Works, Artists WHERE Artists.ArtistNumber = Works.ArtistNumber AND Works.Type = '#FORM.ThisType#' cfloop query=getActiveWorks AND Works.ThisInventory '#getActiveWorks.ThisReference#' /cfloop/cfif ORDER BY Works.Title Asc /cfquery Where the cfloop is is where the problem is going to come into play when the Active Works get to a certain level and the Query becomes Too Complex. What is the Best way to handle this? I am using CF8, Windows Server 2003, and MS Access w/Unicode ODBC Connector. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311307 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query Too Complex for Access?
Have you tried: AND Works.ThisInventory not in (#listQualify(valueList(getActiveWorks.ThisReference),')#) ?? Dave -Original Message- From: David Moore [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2008 1:18 PM To: CF-Talk Subject: Query Too Complex for Access? I know I am setting myself up for another Query too complex issue, so before I start I thought I would ask for suggestions. I run into this when I have to reference two different Access databases that are Client imposed (don't ask). Basically, I have to use one for active data and one to show available date (minus the active data). This is, of coures, where the problem comes in. The queries will help: cfquery name=getActiveWorks datasource=#DSN# SELECT * FROM Works WHERE Works.PageReference = #FORM.ThisPage# AND Works.TypeReference = '#FORM.ThisType#' /cfquery cfquery name=getWorks datasource=#DSN2# SELECT * FROM Works, Artists WHERE Artists.ArtistNumber = Works.ArtistNumber AND Works.Type = '#FORM.ThisType#' cfloop query=getActiveWorks AND Works.ThisInventory '#getActiveWorks.ThisReference#' /cfloop/cfif ORDER BY Works.Title Asc /cfquery Where the cfloop is is where the problem is going to come into play when the Active Works get to a certain level and the Query becomes Too Complex. What is the Best way to handle this? I am using CF8, Windows Server 2003, and MS Access w/Unicode ODBC Connector. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311309 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query Too Complex for Access?
No I haven't. What you are saying is that I should use valueList to build a full list from all values in the getActiveWorks query and then listQualify to see if any variable matches. Thanks David! I will give it a shot. Does anyone else know of any other ways? David G. Moore, Jr. UpstateWeb. LLC Subject: RE: Query Too Complex for Access? From: [EMAIL PROTECTED] To: cf-talk@houseoffusion.com Date: Wed, 20 Aug 2008 13:27:34 -0500 Have you tried: AND Works.ThisInventory not in (#listQualify(valueList(getActiveWorks.ThisReference),')#) ?? Dave -Original Message- From: David Moore [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2008 1:18 PM To: CF-Talk Subject: Query Too Complex for Access? I know I am setting myself up for another Query too complex issue, so before I start I thought I would ask for suggestions. I run into this when I have to reference two different Access databases that are Client imposed (don't ask). Basically, I have to use one for active data and one to show available date (minus the active data). This is, of coures, where the problem comes in. The queries will help: cfquery name=getActiveWorks datasource=#DSN# SELECT * FROM Works WHERE Works.PageReference = #FORM.ThisPage# AND Works.TypeReference = '#FORM.ThisType#' /cfquery cfquery name=getWorks datasource=#DSN2# SELECT * FROM Works, Artists WHERE Artists.ArtistNumber = Works.ArtistNumber AND Works.Type = '#FORM.ThisType#' cfloop query=getActiveWorks AND Works.ThisInventory '#getActiveWorks.ThisReference#' /cfloop/cfif ORDER BY Works.Title Asc /cfquery Where the cfloop is is where the problem is going to come into play when the Active Works get to a certain level and the Query becomes Too Complex. What is the Best way to handle this? I am using CF8, Windows Server 2003, and MS Access w/Unicode ODBC Connector. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311310 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query Too Complex for Access?
Acutally, the first part is correct. The listQualify() function actually just places 'single quotes' around each of the values in your valuelist since that would be required by the DB. List qualify doesn't check any variables. I noticed you had a stray /cfif tag. Were you missing a cfif condition as you only wanted to compare against 'some' of the records in getActiveWorks? If so, send your CFIF statement as we'll have to modify what I sent you earlier. Dave -Original Message- From: David Moore, Jr. [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2008 1:37 PM To: CF-Talk Subject: RE: Query Too Complex for Access? No I haven't. What you are saying is that I should use valueList to build a full list from all values in the getActiveWorks query and then listQualify to see if any variable matches. Thanks David! I will give it a shot. Does anyone else know of any other ways? David G. Moore, Jr. UpstateWeb. LLC Subject: RE: Query Too Complex for Access? From: [EMAIL PROTECTED] To: cf-talk@houseoffusion.com Date: Wed, 20 Aug 2008 13:27:34 -0500 Have you tried: AND Works.ThisInventory not in (#listQualify(valueList(getActiveWorks.ThisReference),')#) ?? Dave -Original Message- From: David Moore [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2008 1:18 PM To: CF-Talk Subject: Query Too Complex for Access? I know I am setting myself up for another Query too complex issue, so before I start I thought I would ask for suggestions. I run into this when I have to reference two different Access databases that are Client imposed (don't ask). Basically, I have to use one for active data and one to show available date (minus the active data). This is, of coures, where the problem comes in. The queries will help: cfquery name=getActiveWorks datasource=#DSN# SELECT * FROM Works WHERE Works.PageReference = #FORM.ThisPage# AND Works.TypeReference = '#FORM.ThisType#' /cfquery cfquery name=getWorks datasource=#DSN2# SELECT * FROM Works, Artists WHERE Artists.ArtistNumber = Works.ArtistNumber AND Works.Type = '#FORM.ThisType#' cfloop query=getActiveWorks AND Works.ThisInventory '#getActiveWorks.ThisReference#' /cfloop/cfif ORDER BY Works.Title Asc /cfquery Where the cfloop is is where the problem is going to come into play when the Active Works get to a certain level and the Query becomes Too Complex. What is the Best way to handle this? I am using CF8, Windows Server 2003, and MS Access w/Unicode ODBC Connector. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311312 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query Too Complex for Access?
I noticed that after I hit the 'send' button. I had a cfif to check if there were actual records before running the statement. I didn't think I needed to show all that, so I took it out, but left the stray end code. The code works well. I haven't tested it at a lot of values though. This will not have the same issue once their are like 100 records in the getActiveWorks query. Right? ~David G. Moore, Jr. Subject: RE: Query Too Complex for Access? From: [EMAIL PROTECTED] To: cf-talk@houseoffusion.com Date: Wed, 20 Aug 2008 15:18:11 -0500 Acutally, the first part is correct. The listQualify() function actually just places 'single quotes' around each of the values in your valuelist since that would be required by the DB. List qualify doesn't check any variables. I noticed you had a stray /cfif tag. Were you missing a cfif condition as you only wanted to compare against 'some' of the records in getActiveWorks? If so, send your CFIF statement as we'll have to modify what I sent you earlier. Dave -Original Message- From: David Moore, Jr. [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2008 1:37 PM To: CF-Talk Subject: RE: Query Too Complex for Access? No I haven't. What you are saying is that I should use valueList to build a full list from all values in the getActiveWorks query and then listQualify to see if any variable matches. Thanks David! I will give it a shot. Does anyone else know of any other ways? David G. Moore, Jr. UpstateWeb. LLC Subject: RE: Query Too Complex for Access? From: [EMAIL PROTECTED] To: cf-talk@houseoffusion.com Date: Wed, 20 Aug 2008 13:27:34 -0500 Have you tried: AND Works.ThisInventory not in (#listQualify(valueList(getActiveWorks.ThisReference),')#) ?? Dave -Original Message- From: David Moore [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2008 1:18 PM To: CF-Talk Subject: Query Too Complex for Access? I know I am setting myself up for another Query too complex issue, so before I start I thought I would ask for suggestions. I run into this when I have to reference two different Access databases that are Client imposed (don't ask). Basically, I have to use one for active data and one to show available date (minus the active data). This is, of coures, where the problem comes in. The queries will help: cfquery name=getActiveWorks datasource=#DSN# SELECT * FROM Works WHERE Works.PageReference = #FORM.ThisPage# AND Works.TypeReference = '#FORM.ThisType#' /cfquery cfquery name=getWorks datasource=#DSN2# SELECT * FROM Works, Artists WHERE Artists.ArtistNumber = Works.ArtistNumber AND Works.Type = '#FORM.ThisType#' cfloop query=getActiveWorks AND Works.ThisInventory '#getActiveWorks.ThisReference#' /cfloop/cfif ORDER BY Works.Title Asc /cfquery Where the cfloop is is where the problem is going to come into play when the Active Works get to a certain level and the Query becomes Too Complex. What is the Best way to handle this? I am using CF8, Windows Server 2003, and MS Access w/Unicode ODBC Connector. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311315 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query Too Complex for Access?
David, I don't know if it will or not, you will just need to test. I'm sure there is some upper limit as to how many bytes you can send in a call to the DB, but I'm also betting that's driver dependent. Oh, if there is a possibility that getActiveWorks might be empty, you will want this: cfif getActiveWorks.recordCount AND Works.Inventory NOT IN (#listQualify(valueList(getActiveWorks.ThisReference),')#) /cfif If you don't have that condition around it, you could end up with this SQL, which would bomb: AND Works.Inventory NOT IN () If your app is going to exceed some limit, you may need to break your query down somehow. Hopefuly that won't be an issue though. Hope this helps! Dave -Original Message- From: David Moore, Jr. [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2008 3:48 PM To: CF-Talk Subject: RE: Query Too Complex for Access? I noticed that after I hit the 'send' button. I had a cfif to check if there were actual records before running the statement. I didn't think I needed to show all that, so I took it out, but left the stray end code. The code works well. I haven't tested it at a lot of values though. This will not have the same issue once their are like 100 records in the getActiveWorks query. Right? ~David G. Moore, Jr. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311318 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query Too Complex for Access?
Does anyone else know of any other ways? Plenty of them, but no one is better ;-) ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311342 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
