I already searched the mailing list archive and couldn't find a similar
problem. So, here it is.
A DEPENDENT QUERY PROBLEM
Suppose we want to execute the following query Q, which depends on queries
q1 and q2. These queries are given below.
The tables reside in a Microsoft Access97 database file. Restriction: we
must use these database, at least for now.
I want to display the query results in a web application using JSP pages
built with Jakarta Tag Libraries, especially DBTags Pre-Beta version 1.0
(downloaded 10/2/01). I'm using Tomcat 3.2.3 and J2SE 1.3 on a Windows98
machine.
Query Q:
SELECT tblInvoice.CustomerID, tblInvoice.InvoiceID,
tblInvoice.DatePrepared, tblInvoice.InvoiceAmount, tblInvoice.Open,
TotalCredits, TotalPayments
FROM tblCustomer RIGHT JOIN
((tblInvoice LEFT JOIN q1 ON tblInvoice.InvoiceID = q1.InvoiceID) LEFT JOIN
q2 ON tblInvoice.InvoiceID = q2.InvoiceID)
ON tblCustomer.CustomerID = tblInvoice.CustomerID
WHERE (((tblInvoice.DatePrepared)<#<%= request.getParameter("startDate")
%>#) AND ((tblInvoice.Open)=True))
ORDER BY tblInvoice.CustomerID, tblInvoice.InvoiceID;
Query q1:
SELECT tblCredit.InvoiceID, Sum(tblCredit.Amount) AS TotalCredits
FROM tblCredit
WHERE (((tblCredit.Date)<=#<%= request.getParameter("startDate") %>#))
GROUP BY tblCredit.InvoiceID;
Query q2:
SELECT tblAllocation.InvoiceID, Sum(tblAllocation.AmountPaid) AS
TotalPayments
FROM tblPayment RIGHT JOIN tblAllocation
ON tblPayment.PaymentID = tblAllocation.PaymentID
WHERE (((tblPayment.Date)<=#<%= request.getParameter("startDate") %>#))
GROUP BY tblAllocation.InvoiceID;
Note that all these queries depend on the request parameter "startDate".
Queries q1 and q2 execute separately on different JSP pages without problem.
Question:
Using Jakarta DBTags Tag Library, how can this problem be solved without
using temporary or permanent working tables to hold results for q1 and q2?
A solution based on working tables:
Using temporary or permanent working tables, the problem can be solved by
first executing q1 and q2 and inserting the results in working tables wtq1
and wtq2, respectively. Then, query Q can be computed based on these tables.
A big drawback of this solution is poor performance and db size increase.
Permanent working tables increase the size of the Access database,
considerably in some cases . Temporary working tables must be created and
deleted every time the associated queries are executed, which produces a
performance degradation in proportion to the frequency of computation of the
queries. More important, in some cases, including this one, regular users do
not have permission to create tables, therefore, the use of temporary work
tables is out of the question.
A solution without working tables?:
Using the <sql:query> tag, we can execute q1, which will produce resultSet
rset1. Then we can execute q2 to obtain resultSet rset2. (In both cases we
must use a resultSet per Statement.) One question is:
(1) How to refer to queries q1 and q2, for example, in the following segment
of query Q so that q1 and q2 are treated as if they were regular tables?
((tblInvoice LEFT JOIN q1 ON tblInvoice.InvoiceID = q1.InvoiceID) LEFT JOIN
q2 ON tblInvoice.InvoiceID = q2.InvoiceID)
If there is a valid way to refer to q1 and q2 in the above statement, I
guess the problem can be solved.
(2) Also, is there any other way to compute query Q using DBTags?
I think we can use the Java scriplet <% int rset1.getInt(1); %> instead of
q1.InvoiceID in the above expression as long as the scriptlet is in the
scope of rset1. A similar expression can replace q2.InvoiceID.
I would really appreciate any help you can provide me.
Thanks a lot,
Alonso E. Rhenals
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
