Re: [DFW CFUG] Strange CF Error

Nate Nielsen Sat, 23 Sep 2006 20:38:03 -0700

From what I gather in your description, shouldnt the query of query be "From #TableName#" and not "From ShipHist" ?

----- Original Message -----

From: Loyd Campbell

To: [email protected]

Sent: Saturday, September 23, 2006 1:44 PM

Subject: [DFW CFUG] Strange CF Error

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 doesnt. 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

Plano, TX

_______________________________________________
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/

_______________________________________________
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/

Re: [DFW CFUG] Strange CF Error

Reply via email to