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

[CRPence <crp@xxxxxxxxxxxxxxxxxxxx>]

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