I've read the Derby developer's guide and Rick Hillegas's informative white paper (http://developers.sun.com/javadb/reference/whitepapers/sampleTableFunctions/doc/TableFunctionsWhitePaper.html) on Table Functions, but am still struggling with the following issue:
I am trying to create an RDB abstraction for a large CICS/VSAM-based legacy system and blend it with our newer, RDB-based tier. This seems like a good application of TableFunctions. The VSAM data is made available to me via an IP-based proprietary messaging interface. There are lots of different files here, but due to some historical forces, most of the data I'm interested in resides in 4 VSAM files. Unfortunately, each of those VSAM files has over a 1000 fields in it. Now eventually, it might be possible to fully model a single VSAM file into (for the sake of argument) 50 tables; each table/row representing a small slice of a single VSAM record. In the meantime, for both this proof-of-concept and as a migration path to our existing clients, I'd like to represent each VSAM file as a table (subject to the 1024 column SQL limitation per table). This will be a highly-denormalized and decidedly non-relational view of the data, but it will be easy to demonstrate and immediately recognizable to our customers. However, I can't seem to get around the problem of data granularity. For example, if my customer executes: select house_number, street, city from table (legacy_realty_data()) where price < 500000 I don't appear to have any visibility to the actual query inside my legacy_realty_data TableFunction, so I have to go get all 1000 fields for however many listings are present where price< 500000 even though only three columns will be requested. Am I missing something? Aside from having the user repeat the columns as parameters to the table function (which looks awkward to say the least), I can't see a way around this based on my limited knowledge of Derby. Is there a way to only retrieve the columns that the user is querying for? Looking forward to your help/advice. -chris
