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
of adding the max(rank) subselect is just bad from every possible
It's not particularly readable, and even if it is, it's additional
that's simply not needed.  The idea of adding extra I/O just because
want to use SQL is a really poor idea.  This same thing could be done
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
going to continue to cause system bloat.  If you don't know RPG, learn
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
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
of the file, but hey, if you aren't worried about performance, this is
as valid a technique as any.


This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
To post a message email:
To subscribe, unsubscribe, or change list options,
or email:
Before posting, please take a moment to review the archives

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.