RE: Can you do this in SQL

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> From: David Morris
>
> 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."

Think this through carefully before you make this statement, David.  Do you
really want to update the rank every time you add a record?

Add record 1, dollars = 10
Trigger sets rank to 1

Add record 2, dollars = 20
Trigger sets rank to... 1!
Which means the trigger ALSO has to set the first record to 2.

It gets REALLY interesting when the millionth record you add has the highest
dollar amount.  Press enter, watch lights dim.

I'm not saying triggers are inherently bad, but I am saying that there are
things that just don't belong in a trigger, and a numeric ranking based on a
field is probably right up there among the things you don't want to do.

And in this particular case, I am absolutely saying that, for the iSeries,
the best solution is an OPNQRYF and a 4-line RPG.  Even an SQL cursor/fetch
has more lines and I'm willing to bet it's not any faster.


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

I agree, but that's the reasonable hypothesis here - a batch udpate or a
one-shot.  It's definitely not something you do on a record by record basis.