Walden H. Leverich wrote:
Of course not. You have a new row for each item when the price changes.
That is the point of the subselect, which I now see has a bug. :) The
subselect should have been:
(select top 1 IP.Price from ItemPrice IP
where IP.Item# = I.Item#
and IP.EffDate <= O.OrderDate order by EffDate Desc)
One other little issue with SQL, and this is the really important bit,
so I gave it it's own response.
Your example shows getting the price. Now, let's make it a little more
complex. While I have a problem with your premise of calculating this
on the fly the way you are, I'll go with it, but let's add some other
functions: you'll also want to get the tax and the shipping charges.
Now if you were to do all of that in a single SQL statement, it would be
quite large and complicated. Not only that, if you had a different
program that needed the weight but not the tax, you'd have to write a
new SQL statement, copying subselects from one SQL statement to another.
With native I/O, you can easily subdivide the workload into specific
tasks: get price, get tax, get weight, get shipping charge, and so on.
These pieces are easy to reuse, and even better easy to unit test. In
order to take advantage of the multiple-file capabilities of SQL, you
have to do all of that in one statement, a single statement that is
unlikely to be of any use anywhere else in your system. Sure, you may
have to calculate the weight again, but that will be a different SQL
statement. The syntax will be the same, so you can copy part of one
statement to another, but you're now duplicating logic - if the weight
calculation changes, you have to find and change every SQL statement
that uses it!
If, though, you granularize the logic, then you're no longer gaining the
benefit of accessing multiple files at the same time. You're instead
reading at most a couple of related files for each function and probably
re-reading some files in more than one function.
To me, the granularity of programming in native I/O leads to all the
benefits of modern programming: reuse, encapsulation, agile development,
unit testing, all hat good stuff. The biggest benefit of SQL is you can
test it on a command line like BASIC. Great for queries, not such a
benefit in shop floor control.
I guess the only way to really know for sure is to sit down and compare
a system written in native I/O with one written entirely in SQL. So, if
the SQL side could get me a copy of, say, an MRP generation written in
SQL and we can compare it to the same logic in native I/O.
Joe
midrange.com
