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:253978
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
