Mega Code Archive

Now children, one question at a time!

The Perpetual Newbie - Log Entry #7.1 This article first appeared on http://www.undu.com Normalized data is great. One instance of any individual datum, and never keep around any data that can be extrapolated from other data on the fly. Lean, mean and occasionally a gigantic pain. A customer asked me a simple question. Can we see an inventory report of only parts that belong to a raw material group that has more than one part in it? This would allow for better overall viewing of the raw material needs long term. (We have Min/Max and on Demand automatic raw material purchase red flag lists already. They wanted to see the global view of multiple use raw material) Simple. I'll ask the inventory database for all parts where the RMG (Raw Material Group code) has more than one instance. In Paradox, I'd do a CALC COUNT ALL query on the RMG, then a second query asking for all records in the Inventory database that are in the temporary answer table. Paradox would clean up the answer table upon my being finished with it. The whole coding exercise would be trivial. But how to do it in Local SQL using the same Paradox databases for Delphi? An answer that DID work was to do the same queries as above, but I had to take care to create the answer database in real life and then delete it after the fact. Noisome. Besides, was not there some way to do this in one super SQL statement? Something about nested statements? It's a lie folks. Nested SQL does not seem to work with Local SQL being performed on Paradox tables. After some head-thumping, I admitted defeat and headed off to the Tamarack search site (developers.href.com) and started searching. I did two searches before I encountered the wisdom of one Steve Koterski of Borland. Seems he's the Local SQL wiz. By extrapolating some of his answers and testing a bit, I discovered a nice, clean solution to the problem. And this solution has other applicability if you continue to use Paradox databases in your applications. First, I created a SQL file called "CountRMG.SQL." This is the equivalent of my first query from above: /* Alias: THEAPP LiveAnswer: FALSE */ select RMG, count(RMG) as MULTI from "INVENTRM.DB" GROUP BY RMG This is a simple example, but you could do sum totals just as easily. The idea is to get some aggregate data. This leads to the actual query used in the program code: SELECT R.* FROM "INVENTRM.DB" R, "COUNTRMG.SQL" M WHERE ((R.RMG=M.RMG) and(M.MULTI > 1)) The secret is the linking of the second 'virtual' table via the saved SQL code. It will load and run and offer itself as an temporary in-memory table to filter INVENTRM with. The possibilities of using this faux nested SQL are certainly not limited to doing counts to find multiple instances of a secondary index field. As mentioned, you could do sums for a date range and produce all items that fall into specifics in that range. Almost all of them will lead to a two-step query and to the answer you need. Gary Mugford Idea Mechanic, Bramalea ON Canada mugford@aztec-net.com