|
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com 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.