SQL to process invoice header with two matching detail files

[J.Pluta]

Why not use a simple RPG program for this? It could either use native I/O or run the same embedded SQL statement over invdlta and invdltb, then add the results. Joe

[coteijgeler@chello.nl]

Richard, Have you tried UNION? Something like SELECT ....... FROM filea, fileb WHERE .... UNION SELECT ....... FROM filea, filec WHERE .... ORDER BY .... The resultsets of both quries should have the same number of columns with the same data type. Regards, Carel Teijgeler.

[Absolutely_Nobody]

Joe, I want to learn how to access the files in this manner in the event that we move it off to a client/server environment......maybe eventually Java using JDBC or whatever. It would probably be better to use the the RPG as a stored procedure and use the AS/400 to do the "back-end" processing. Then, call the stored procedure to pass the data back.

[Absolutely_Nobody]

Carl, Tried the union select and response time was absolutely unacceptable.

[Guest.Visitor]

Richard, I've never seen syntax like that before, let's try something else: If there's always at least one record in both detail files: From invhdr Join invdtla on invhdr.cust# = invdtla.cust# and invhdr.inv# = invdtla.inv# and invhdr.invpfx = invdtla.invpfx Join invdtlb on invdtla.cust# = invdtlb.cust# and invdtla.inv# = invdtlb.inv# and invdtla.invpfx = invdtlb.invpfx If you don't know the population of the detail files: From invhdr Left Outer Join invdtla on invhdr.cust# = invdtla.cust# and invhdr.inv# = invdtla.inv# and invhdr.invpfx = invdtla.invpfx Left Outer Join invdtlb on invdtla.cust# = invdtlb.cust# and invdtla.inv# = invdtlb.inv# and invdtla.invpfx = invdtlb.invpfx Bill Richard Rothe wrote: > I have a invoice header file with two invoice detail files. I need to > calculate profit (cost - price) for a given customer for a given > month. I have tried using nested inner joins, but it gives me the > same a.detail for each b.detail. > > I'd also like to know how I can summarize the data for the customer > and get a count of invoice header records. > > This is the SQL statement that I have..... > > > > Code <'http://www.mcpressonline.com/mc/showcode@@.6ae56d15'>

[J.Pluta]

Absolutely. Even if you move to a distributed environment (such as client/server), you should still keep all your database logic on the same machien as the database. ODBC (and it's Java counterpart, JDBC) should be used very sparingly on the client. I just taught a week-long onsite training seminar on client/server architecture, and it's amazing what you can do without a single line of fat-client logic. Joe

[Absolutely_Nobody]

Bill, Detail B is "additional billing" and may not have a matching record. So, I used your second example and it gave me the same result as what I had originally posted.

---

Code

[Guest.Visitor]

Richard Rothe wrote: > Bill, > > Detail B is "additional billing" and may not have a matching record. > So, I used your second example and it gave me the same result as what > I had originally posted. Ok, let's go back to the beginning then - I may not understand the problem. Is the problem that you just want to select it summarized by customer, or something else? Bill

[Absolutely_Nobody]

I believe that the most appropriate solution is what Joe offered. That is, do it in RPG and make it a stored procedure that could be called by the client, whether the client is Java or .NET. All of the "experts" seem to say the same thing. Just yesterday, i read Sharon Hoffman's article in the "other publication". She advised the stored procedure may be the best solution in this case. To that end, I appreciate the input from everyone.

[rv_ooty@hotmail.com]

If you know VB, use the data environment. You can do amazing reports or queries without having one line of code written. Once done the SHAPE SQL command in DE inside VB, right click and copy the heirarchical command. Run it as a normal SQL open statement from VB on AS400 connection. There you are with the recordset with all what you wanted. just set the datasource of a hierarchical grid to this recordset. Pooh!!! You have everything. Did you write any code??? RV rv_ooty@hotmail.com