|
I was on this list years ago, but dropped off a couple of
years ago. Now I have a problem and am wondering if anyone might have an
idea of what is happening. 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 older 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 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. In order to
speed up the queries, I run 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. 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 dates are
old enough to look for the SHIP_HIST_ARC table, ColdFusion throws an error; The
initial query runs fine, but one of the query of queries doesn’t. 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 shows 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 sense to me if there was an Oracle error, 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 views of similarly constructed tables 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 |
_______________________________________________ Reply to DFWCFUG: [email protected] Subscribe/Unsubscribe: http://lists1.safesecureweb.com/mailman/listinfo/list List Archives: http://www.mail-archive.com/list%40list.dfwcfug.org/ http://www.mail-archive.com/list%40dfwcfug.org/ DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/
