RE: Can you do this in SQL

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.

David Morris

> From: R. Bruce Hoffman, Jr.
>
> hhhhmmmmmmmmm......
>
> with inRank (cust, doll) as (select customer, dollars from
> rankingFile order
> by dollars desc)
> update rankingFile
>   set rank  = (select max(rank) + 1 from rankingFile)
>   where customer = cust
>        and rank = 0
>
> it's just too bad that SQL chokes on this... the 400 won't allow
> order by on
> the common table expression and the 400 won't do the recursion
> and it won't
> allow update from the common table expression.
>
> so the short answer appears to be "no, not with just SQL."

Even if you COULD make this statement work, why would you want to?  The
idea
of adding the max(rank) subselect is just bad from every possible
aspect.
It's not particularly readable, and even if it is, it's additional
overhead
that's simply not needed.  The idea of adding extra I/O just because
you
want to use SQL is a really poor idea.  This same thing could be done
using
an OPQNRYF and a four-line RPG program.

Input Primary F-spec
add i to counter
move counter to rank field
update record

This unreasonable desire to use SQL even where it's a really bad fit
is
going to continue to cause system bloat.  If you don't know RPG, learn
it.
The syntax is pretty darned simple (especially compared to SQL!) and
it's an
incredibly powerful tool.

Finally, there IS a way to do this with SQL, though it takes two
steps.
First, use a SELECT with ORDER BY to create a work file in the correct
sequence.  Then, update your field using an UPDATE MYFILE SET RANK =
RRN(MYFILE).  Here, of course, you have the overhead of creating a
duplicate
of the file, but hey, if you aren't worried about performance, this is
just
as valid a technique as any.

Joe

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.