Copy link to clipboard
Copied
Why is this query....
<cfquery datasource="manna_premier" name="kit_report">
SELECT Orders.ID,
SaleDate,
Orders.UserID,
Distributor,
DealerID,
Variable,
TerritoryManager,
US_Dealers.ID,
DealerName,
DealerAddress,
DealerCity,
DealerState,
DealerZIPCode,
(SELECT SUM(Quantity)
FROM ProductOrders PO
WHERE PO.OrderID = Orders.ID) as totalProducts,
FROM Orders, US_Dealers
WHERE US_Dealers.ID = DealerID AND SaleDate BETWEEN #CreateODBCDate(FORM.Start)# AND #CreateODBCDate(FORM.End)# AND Variable = '#Variable#'
</cfquery>
giving me this error message...
Error Executing Database Query. | ||||||||
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. | ||||||||
The error occurred in D:\Inetpub\mannapremier\kit_report2.cfm: line 20 | ||||||||
18 : WHERE PO.OrderID = Orders.ID) as totalProducts, 19 : FROM Orders, US_Dealers 20 : WHERE US_Dealers.ID = DealerID AND SaleDate BETWEEN #CreateODBCDate(FORM.Start)# AND #CreateODBCDate(FORM.End)# AND Variable = '#Variable#' 21 : </cfquery> 22 : | ||||||||
| ||||||||
Resources: |
I copied it from a different template where it works without error...
<cfquery name="qZVPData" datasource="manna_premier">
SELECT UserID,
TMName,
UserZone,
(SELECT COUNT(*)
FROM Sales_Calls
WHERE Sales_Calls.UserID = u.UserID) as totalCalls,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.UserID = u.UserID) as totalOrders,
(SELECT SUM(Quantity)
FROM ProductOrders PO
WHERE PO.UserID = u.UserID AND PO.NewExisting = 1) as newItems,
(SELECT SUM(NewExisting)
FROM ProductOrders PO_
WHERE PO_.UserID = u.UserID) as totalNew,
SUM(totalOrders)/(totalCalls) AS closePerc
FROM Users u
WHERE UserZone = 'Central'
GROUP BY UserZone, UserID, TMName
</cfquery>
What is the problem?
Copy link to clipboard
Copied
Might "variable" be a reserved word in Access?
Copy link to clipboard
Copied
No I don't think so because if I remove the sub-query it will execute without error.
Copy link to clipboard
Copied
Take the generated sql and run it directly in Access. What are the results? (Obviously reformat the odbc dates {d '2009-10-31'} first)
Copy link to clipboard
Copied
I tried that and it also gives me the exact same error message. I don't understand why this won't work.
Copy link to clipboard
Copied
When all else fails, try something different.
In another thread I suggested commenting out parts of the query. To help narrow down the source of the problem? For example, just run the JOIN portion alone. Comment out the SELECT list columns, and use SELECT * for now.
If that does not work, your problem lies somewhere within the basic join or where clause. If it DOES work, uncomment a few columns from the original SELECT list. Keep doing that until you hit an error. Then at least you will know which line is causing the error and can figure it where to go from there.
-Leigh
Copy link to clipboard
Copied
I have tried all the troubleshooting hints you have mentioned (I learned a lot from you), but no luck. I have isolated it to the following...
(SELECT SUM(Quantity)
FROM ProductOrders PO
WHERE PO.OrderID = Orders.ID) as totalProducts,
If I comment this out the query executes without error. As I mentioned before this subquery runs flawlessly in a different template. I can't figure out why it won't run here. Any thoughts?
Copy link to clipboard
Copied
For starters, the query you show us isn't the one that's erroring.
It helps if you troubleshoot the right thing 😉
Secondly, there's a syntax error on the query that is erroring. You've got a trailing comma in your main SELECT clause.
--
Adam
Copy link to clipboard
Copied
I corrected the coma issue and the query executes but returns only one result and I know there are more results. When I use WHERE criteria that I know will return a lot of results it gives me a timeout error. Please help!
Copy link to clipboard
Copied
The request has exceeded the allowable time limit Tag: cfoutput
OK, so the error message isn't that mysterious. You've got a request timeout set, and the template is taking longer than that to run.
For the purposes of debugging, I'd switch your request timeout off until you get this issue sorted. Once that's done, go back to addressing why your template is taking so long to run.
What do you mean about not showing the right query?
Now this is strange.
When I replied to your post, I was seeing the a different query in the top part of your first post to that in the error message. The top query looked pretty much the same as the second one you cite. I thought it was a bit odd, so did verify I was not misreading things... I thought... 😕
--
Adam
Copy link to clipboard
Copied
Did you see the edited version of my last post? It seems to be having a problem with multiple results. If I use Where criteria that I know should give me two results but I only get one. Any criteria with more results than that and I get the timeout error.
Copy link to clipboard
Copied
I have this query now working correctly on 6 out of 7 #Variable# criteria. It times out when it searches for Variable 1, which contains 711 records. The next highest recordcount for Variable is only 82, the rest are even less. Would 711 records cause a time out? Should I be looking at something else?
Copy link to clipboard
Copied
It's hard to say: what's your request timeout set to?
700-odd records is not much of a fetch for a decent DB, and I would not expect that to case the problem. But then you're using Access which doesn't fit the description of "decent DB" (or "fit for purpose" or "intended for purpose"), so I guess all bets are off one that one. If this query is slow when ONE request is asking for it, what is going to happen when it goes live and multiple requests are asking for it, along with all the other queries your site will want to run? Access is not designed for this. It will really struggle, and cause your site to run like a dog. One that died serveral weeks ago.
What else is on the template? I presume you're doing something with the query once you fetch it, so could it be that code that's running slowly? Have you taken any steps to isolate which part of the code is taking so long?
How does the query perform if you take the subquery out of the select line? Is there any other way of getting that data? What subquery will be running once for every row of the result set... not very nice.
--
Adam
Copy link to clipboard
Copied
I have this query now working correctly on 6 out of 7 #Variable# criteria. It times out when it searches for Variable 1, which contains 711 records. The next highest recordcount for Variable is only 82, the rest are even less. Would 711 records cause a time out?
I expect not.
Should I be looking at something else?
Yes, parts of the query that might search deeper than you expect. For example, shouldn't that clause be something like this
WHERE US_Dealers.ID = Orders.DealerID