And I suppose there are instances where it makes
sense.
Just not every instance.
Just in case anybody misses my point here, let me be clear. I think SQL
and RLA are both great tools. I use SQL all the time - ALL the time.
There are things I can do with SQL that simply aren't practical with RLA.
That's not to say they can't be done, they're just a lot easier in SQL.
I also use RLA. Let me give you a very simple example. Let's say you
want to create the material requirements for a work order. You have an
item, a quantity and a bill of materials. Simple enough in RLA - read the
bill, multiply the quantities. EVEN EASIER in SQL: it's a standard
set-based calculation, so away you go!
Now let's get real world. Add just a couple of standard capabilities.
First, you have a multi-level bill with phantoms. That's recursion. It's
doable in SQL, but it ain't easy. I know, I teach recursion with CTEs and
it's far from intuitive.
Next, you can have alternate bills. The alternate bills can occur at any
point in the bill of materials. The trigger for using a bill is whether
one exists for the facility. Very easy to do in RLA - do a SETLL and
check %equal. Not quite so easy in SQL, especially in the middle of your
recursive CTE.
Finally, you can have substitute materials. You use these if you don't
have enough onhand for the current build. Again, pretty easy to do for
the RLA - once you calculate the quantity required, do a calculation for
the onhand and if it's not available, attempt the same thing for the
substitute materials. It gets really interesting when the substitute
materials are customer specific (i.e., certain customers allow
substutions, others don't, and they have different items they'll allow).
This is all second nature for RLA and relatively easy to do.
The real problem with this in a pure SQL environment is that you're going
to be slamming all of this code into the big INSERT statement that
generates the requirements for this work order. Just the couple of
conditions I outlined above would make a single SQL statement all but
unreadable and definitely undebuggable.
At least with embedded RPG you don't have to use a single SQL statement -
you could do a bunch of little cursors and SELECT WHERE EXISTS and so on,
but I guarantee that your performance will suffer, in some cases
significantly. And if you're using embedded SQL in RPG, you remove a
couple of the big benefits of an all-SQL environment: portability and the
ability for non-i programmers to maintain the code.
So, once again it comes down to a simple business decision: do you want to
trade maintainability and performance for the ability to make changes to
your database fields a little more easily?
I could have been more politic about it. But I get a little cranky
hearing that perfectly good tools are bad because they don't meet a
preconceived notion of what's current and correct. I've had to fight that
battle for way too many years.
Anyway, as always your mileage may vary. But RLA still has a lot of good
years in it, just as does RPG and the IBM i itself.
Joe
midrange.com
