In standard SQL, I know that when you do a SELECT COUNT() you ALWAYS get back a row. However, in ColdFusion query of queries, if you do a SELECT COUNT() on a table that has no rows, the query -of-queries table actually does not return any rows. It is possible that you are expecting a value of "0" (zero) as the count, but in fact, the query of queries has a record count of Zero??
...................... Ben Nadel Certified Advanced ColdFusion Developer www.bennadel.com -----Original Message----- From: Loyd Campbell [mailto:[EMAIL PROTECTED] Sent: Saturday, September 23, 2006 3:02 PM To: CF-Talk Subject: Error 0 - java.lang.ArrayIndexOutOfBoundsException I'm querying data that is contained in either a current table, SHIP_HIST, or an archive table, SHIP_HIST_ARC. There is a view setup on the server, SHIP_HIST_VIEW, that does a union on both tables so if a user wants to query both old and new data they may. A nightly stored procedure moves records from current to archive tables based on age, so there is no duplication of records between the two tables. The current table is kept fairly small so most queries can be done quickly. The current version of the report I am working on always queries the view, regardless of what dates the user is looking for. This is a very slow process due to the large number of records in the view. In order to speed up the queries, I am running a quick query to determine the oldest date in the current table, and as a result of that I name a variable "TableName". If the begin and end dates the user is looking for are entirely within the current table, TableName is SHIP_HIST. If the begin and end dates are both before the oldest date in the current table, the TableName variable is SHIP_HIST_ARC. If the begin and end dates span the oldest date in the current table, then I name TableName SHIP_HIST_VIEW. This query works fine every time. Then, I run queries against this initial query to get some subtotals and grand totals. My problem is that when the initial query is run against either SHIP_HIST or SHIP_HIST_VIEW, it runs fine. If the initial query is run against the SHIP_HIST_ARC table, ColdFusion throws an error; The header on the debug page is "Error Occurred While Processing Request". The data is a single zero (0). Below that is the area that shows the filename where the error occurs and then show where this data is being called from. I have all the queries in separate files so the error is occurring in ShipHist5.qry. Here is the text of that query: <CFQUERY NAME="#QueryName#" DBType="query"> SELECT count(distinct mst_ship_num) AS mst_shp_tot_qty FROM ShipHist </CFQUERY> As I said, it runs fine when the initial query is run against either the current table or the view, but not the archive table. It would make more sense to me if the error came from Oracle, but it is a CF error. Down in the Exceptions area of debug here is the error message: Exceptions 13:37:54.054 - java.lang.ArrayIndexOutOfBoundsException - in D:\inetpub\wwwroot\dev\DMPlusReports\queries\ShipHist5.qry : line 12 0 Can anyone make any sense of this error? I have used the same technique to speed up other reports that were originally written against the view and have had no problems. This one has me stumped. I'd sure appreciate anyone who might have an idea what is going on here. Thanks, Loyd Campbell Contract CF Programmer Plano, TX ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:254034 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
