RE: Can you do this in SQL

Joe,

I probably am misinterpreting your message when you
say "people insist on getting away from it (rpg)" and
"my 5-line, two-program solution beats your 100-line, 3
object technique any day of the week" but
maybe you missed this in my message -- "I would
consider moving the rank to another file and updating
it from a short (but not four line) trigger written in RPG."

I agree that SQL is not the solution here and that the
statement that Bruce posted was a bad fit for the
problem. I am also not implying OPNQRYF or RPG are
bad, just that they no more fit the problem than SQL.
I don't think that the overhead of SQL is that much
different than OPNQRYF.

As for UDF's, the complexity is hidden by the UDF
interface; this is a good thing! The advantage is that it
is accessed through a well defined interface that is easy
to use and promotes reuse. OPNQRY does not facilitate
reuse and the interface is not as widely understood. For
one time use I would consider OPNQRYF, if I need to use
something more than once I would use SQL.

I still wouldn't use either in this case unless this is just the
last step in a batch process.

David Morris

>>> joepluta@PlutaBrothers.com 01/29/02 03:31PM >>>
> From: David Morris
>
> Joe,
>
> I agree that this is not a good place to use SQL, but for
> the same reason, OPNQRYF and a four line RPG program
> are just as bad. You can easily do what you describe using
> SQL and an RPG UDF or even SQL and an SQL UDF in
> a single pass. The problem is that it is not dynamic and will
> have to be rerun when data is changed in the file. I would
> consider moving the rank to another file and updating it from
> a short (but not four line) trigger written in RPG.
>
> Check out the message I posted earlier to see how you
> could construct and create that UDF.

Why are OPNQRYF and an RPG program bad, David?  The SQL statement was
bad
because it has a lot of overhead and is difficult to understand.
OPNQRYF
and an RPG program, on the other hand, are very straightforward and
perform
very well.

As to the UDF, why is a 100 line UDF (not counting comments) - which in
turn
is used to generate a module, a service program and UDF function - a
better
answer than a single-line OPNQRYF and a 4-line RPG program?  What if
you
need a different ranking - say an alphabetical sequence number?  You
need a
new UDF!  And you STILL have to write the SQL statement that uses the
UDF.

RPG is an excellent language for doing database I/O.  Why people insist
on
"getting away from it" is beyond my meager intellectual capabilities.
Maybe
it's "new-itis".  In the early part of my career, I was bitten by the
"newest is best" bug and every time I learned a new technique, I
applied it
to every situation, whether it needed it or not.  But as I've gotten
older
and lazier, I've learned that while you shouldn't use a hammer to screw
in a
screw, it's still the best tool in the toolbelt for pounding in a nail.
 In
fact, even though there are situations where a powered nailgun does the
job
better, for many simple jobs, the old-fashioned claw hammer, as
inelegant
and unsexy as it may be, is still the best tool for the job.

I dunno, but I think my 5-line, two-program solution beats your
100-line, 3
object technique any day of the week.