Nice shot Ben. Another longshot: with the underscore notation, fields ending like _date or _someOtherReserved extension can do weird stuff, because they're reserved in CF IIRC. -denny
On 9/25/06, Ben Nadel <[EMAIL PROTECTED]> wrote: > > 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:254180 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
