Re: Need ideas for SQL join select to replace logical view

Joe Pluta wrote:
>> CRPence wrote:
>> IMO the problem is the database design. Historical ownership
>> can not be correctly represented with only a transaction time.
>
> And yet, with a simple ISAM access, we can generate the required
> report with the existing database. It seems to me that it is NOT
> the database design that is at fault, but simply the wrong choice
> of tool.

Of course the historical representation requires two transaction times. When stored as separate rows that is fine with ISAM, but stored in one row is better for SQL. Yes it is true that SQL is often the wrong tool for the job if the data is not stored in a relational database table in a form already conducive to the SQL. In the same manner we could argue against SQL on flat file and stream data.
However the subject is "Need ideas for SQL join SELECT to replace logical view", with the opening text suggesting the desire is to solve "this problem with SQL instead of logicals." The text alluded replacing DDS keyed LFs which are being used with SETGT+READPE, with SQL SELECT, apparently to stop using the files with an ISAM approach -- an apparent attempt to move to using SQL to access the data. Nothing stated in that text precluded changing the database to make the SQL more conducive. There was no claim that "changing the database just isn't an option."
So if SQL is the wrong tool for the given files, then either the files should change, or the move to the SQL maybe should be discouraged.? That is, why should the OP be offered up some possibly complex, ugly, or even poor performing SQL as the tool to solve the problem, when SQL is considered the wrong tool and approach for the given database design? Note that question is not an attempt to disparage the SQL offered, as the SQL I would have offered was the same. But then I considered the row might better offer the whole story for/during reporting, instead of having to inquire of a prior row to infer the owning duration.
I figured it was important to point out, whereas other approaches being offered could have the SQL to work against the unchanged files, that another perspective might be to address the database itself. So while I agree with you, I believe the OP should at least _consider_ changes to the files, for when that may be a better overall approach for achieving the original stated intent.

>> <<SNIP suggested change to the database>>
> That's all well and good, but it also requires more fields as well
> as extra writes to the database. It's hard to consider a database
> design better than another if it increases both storage requirements
> and workload.

I just moved the work from the inquiry to the write; no more writes, just the additional field+storage, and the same subquery logic required, but once per write instead of many per lookup. Since the majority of resources are typically consumed in retrieval and reporting, a change to the database which involves more resources when storing the data while reducing the cost during inquiry, is often a good trade-off. Besides, replacing some DDS keyed logical files used for ISAM, with appropriate SQL INDEX objects to support query activity instead, could reduce the overall storage requirement for the database. And if fewer access paths, then less maintenance on writes as well. Obviously the owner of the data would need to make an evaluation according to the amount of data that will be generated versus reported, along with evaluating the methods applied for both storing and retrieval of that [amount of] data.

Regards, Chuck