Right on TFisher. I played with several different scenarios and concluded your
suggestion makes the most sense overall. One great attribute is it transcends
language.

hcedmondson
Yes, you can place the OPNQRYF and OVRDBF in your RPG.  I have done it before.  
Your file does not have to be input primary.

Your prLevelBreak() subprocedure would use native file I/O to a local file 
(assuming you are at V6) or SQL to determine if there is another record or 
not.  I have created procedures for this that do sort of like a "look ahead" to 
determine whether or not it's time to process totals.

See "Detailed Logic Cycle" in an RPG manual near you...

Is usropn compatible with a primary file? The only way I know of to use opnqryf
with the cycle is to use a CL program and do an override.

Now your line "If prLevelBreak(Data) = *On;" is clean, but the devil is in the
details. What does prLevelBreak look like?

OPNQRYF doesn't require a second program...user controlled file and QCMDEXC 
will do the trick.

If you are going to replace the cycle then why not also replace the native file 
operations with procedure calls:

  // Loop until end of file...
  Dow prReadRecord(Data) <> *Off;
    // process detail
    Callp prProcessDetail(Data);
    // check for level break
    If prLevelBreak(Data) = *On;
      Callp prProcessTotals(Data);
    Endif;
  Enddo;

Inside the procedures you may have SQL or whatever you want/need.

Just to be clear, if I were simply going to process the entire file then I'd not
attempt to bypass the cycle. I've mostly used OPNQRYF in the past to enable
record selection and cycle processing. It's when I started using SQL more, or
even plain old SETLL-READE processing, that I started to miss the ole' cycle.

Programming my own cycle always seemed awkward, which is why I asked the
original question, to learn how others do it.

Anyway, it seems to me the special file technique is somewhat like the OPNQRYF
technique; they both require two programs (in general), and the selection
programs are both somewhat ugly. OPNQRYF uses the CQE versus special which
probably uses the SQE. OPNQRYF is more mainstream, special; not so much. Almost
a coin flip.

So, it's back to how to do the cycle, without the cycle, c'mon, it's fun! It
needs an acronym; CWOC (pronounced swock).

I think we've lost sight of the original question, which was how do you handle
level breaks when you're using SQL, since you don't have the cycle to do "total
time" for you. SPECIAL files may be a little obscure, I don't think anyone's
arguing that, but they appear to provide a mechanism that allows you to use
embedded SQL and still use the cycle.

CurtisB,
That is true, unless there is a bug in the SQL code (special file program).

<I don't think we should refrain from using a technique because a
maintenance programmer might not have seen the technique before>  I agree!  I 
use a ton of subprocedures and I have to answer a ton of questions because of 
that. 

I simply don't see the special file technique as being appropriate for the 
application we've been discussing here.  I would simply use the cycle before 
creating a second program for use as a special file.

TFisher:
Actually I agree with most of what you say. In reality, though, the maintenance
programmer who comes across the SPECIAL file program we've been discussing
doesn't have to know how to CODE the program (unless he wants to write one
himself); he only has to know how to READ it and (quite unlikely) modify it. The
code is transparent enough that any competent programmer that knew SQL and RPG
would know what to do with it.

Of course, when we use our favorite box, we are almost certainly indulging in
the hiding of specifications from the programmer. From the very beginning, when
I first did a S/36 to AS/400 conversion, I remember how frustrated I was by the
fact that if I wanted a real listing of the file layout of an external file, I
would either have to print out the DDS for the file or the file specs generated
in the compile. I could not look at the RPG source and know the layout of the
external file. And it still frustrates me at times. 

I am of the opinion that we should not write obscure code; I don't think we
should write code that would take a maintenance programmer hours or days to
figure out. But I don't think we should refrain from using a technique because a
maintenance programmer might not have seen the technique before. If we had taken
that attitude when RPGIV was devised and subprocedures were first introduced,
subprocedures would not be used today at all, because maintenance programmers
could not be counted on to have seen their use before (since they had never been
used).

I agree Bob...my point was that Special Files are very obscure and less elegant 
and less efficient than subprocedures and ask a programmer that doesn't know 
anything about a subprocedure if they know how to code a special file they most 
likely would not.

Just look at how subprocedures were overlooked here...If it's a "positive" that 
using a special file will allow the embedded SQL to be hidden (or placed 
outside of the mainline) then you have to say that subprocedures share 
that "positivie" aspect.  However, your subprocedure can be embedded right into 
your program and this way NOTHING is hidden from a maintenance programmer who 
may be VERY comfortable with seeing the SQL code.  

This takes me back to RPG-II and RPG-III days on a system I supported where a 
ton of code (C-specs) was hidden by the use of copybooks!  I hated my job back 
then!

Actually, I was aware of SPECIAL files before, but since none of the programs I
worked on ever used them, I never had any interest in investigating them. I like
the fact that the effect of the SPECIAL file usage is invisible to the main
program. Input primary still works the same.

TFsiher has a valid point. I didn't know what was a special file before reading
that link Michael. You cannot ignore that pig in the other room, obviously.

Fish,

>>I would say that there are probably as many programmers out there who don't
>>know about special files as there are programmers who don't know subprocedures.


Sorry Fish, you are dead wrong on this. There are way more programmers who don't
know that a thing called SPECIAL files even exist than there are those who don't
know subprocedures.
:)

CurtisB: "I suppose I have had to dance through the programs of too many people
who created routines that tried to emulate a three or four level deep set of level
breaks without taking advantage of the cycle."

Amen to that. I've penned a few myself, in  VB6 no less. Many attempts to
duplicate the cycle would lead someone unfamiliar with said cycle to wonder
"what are you trying to accomplish here?".

One other positive thought I had about the special file is it does get embedded
SQL out of the mainline. Maybe it's a personal thing but I find SQLRPGLE code
hideous looking, maybe if and when IBM extends it with free-form equivalents
I'll warm up to it.

I suppose I have had to dance through the programs of too many people who
created routines that tried to emulate a three or four level deep set of level
breaks without taking advantage of the cycle. For me, unless I am dealing with
truly massive file reads, clean code trumps performance. And the cycle was
designed to read files in this fashion. It facilitates clean code.

As for maintenance, I thought one of the good points of modularization was to
hide the details from the user. Once we get hold of an API, we really don't care
how it is written, only what it does. The same thing goes for subprocedures; we
can investigate them if we like; but unless they are producing incorrect results
or the programmer needs to change them, that programmer is not likely to feel
the need to look too deeply into them. In this case, if he did look, what the
program was doing would be perfectly obvious. It's unlikely to need change
unless, for example, the SQL needed to be modified.

Likely, the programmer would take a quick look and then move on. A simple
comment in the main code would suffice: "The program called here for this
SPECIAL file uses SQL to read the file in sorted order".   If the programmer is
curious, he will take five or ten  minutes to look up SPECIAL files in any RPG
manual he can get at on paper or PDF. If the comment doesn't tell him what he
wants to know, and if he finds the research too mind-boggling, he probably has
no business attempting to modify the program in the first place.

As a maintenance programmer you cannot ignore that pig in the other room.  I 
would say that there are probably as many programmers out there who don't know 
about special files as there are programmers who don't know subprocedures.

I agree that special files are somewhat intriguing...I would say they are 
probably better suited for things like IFS file processing and things like 
that.  However, I would still see using subprocedures as more elegant, modern, 
and effecient.  Then again, you can still use the RPG cycle.

Well, the mainline RPG program using the special file looks no different from a
program using a disk file, so in that regard it is an elegant solution, just
ignore the pig in the other room :).

I'm not saying "solution found" or that's what I'll use from now on, I found the
special file technique intriguing and I can see some other uses for it going
forward.

So you would rather use Special Files (TWO PROGRAMS) as opposed to another 
simple and more effecient technique that keeps all your code together?  

I thought you were looking for a more "elegant" solution, not an obscure
solution.  Special files have there place, but I don't think they should be 
used in place of the RPG cycle for level-breaks.  

I would use a subprocedure before using a special file since a subprocedure 
call is more effecient than a dynamic call (as far as I know when the system 
calls the special file program it's a dynamic call).

I tried a variation on the theme presented in the SPECIAL file link; Dynamic SQL
allowing the mainline RPG cycle program to tweak what's selected in advance.

The dynamic SQL part is simple, it works as well as the static(?) in the example.

In the mainline during *inzsr, I used QMHSNDPM to send a message to * where the
message data is the SQL statement I want to have run.

In the SPECIAL file program I moved the Declare cursor logic from the (O)pen
section to the (R)ead section and conditioned it with a first-time-only boolean.
The Open code in the SPECIAL file runs before *inzsr in the mainline. Just
before the Declare cursor logic I used QMHRCVPM to receive the message (2 deep
in the call stack) and used the message data as the sql statement to prepare.

Due to the nature of the pre-declared buffer I can't change files but I can see
the value in pre-selecting what records I want from the primary file on the fly.

Guess I had too much time on my hands today, lol.

Michael,

Did you eat a lot of paint chips as a child?

Using an extra copy of the record as an E DS essentially mimics what the cycle
does under the covers. When the program cycle reads the next record, it does not
immediately populate fields with the values from the new record; it brings the
record into a temporary area. It compares control field values in the temporary
area with the program field values, and if there's a level break does total time
calcs and output. Only after this does it move values from the temporary area
into program fields.

I'm in the "use the cycle when it's appropriate" camp, so I really like this SQL
as a SPECIAL file idea. Once you figure it out (thank you Michael for the
reference), it seems like it will be superior to OPNQRYF and other older techniques.

I like it, could even make me use embedded SQL more, thanks.

I tried to write an explanation of this technique but instead just read this:

http://www.itjungle.com/fhg/fhg041305-story01.html

TFisher, yes I would want the record buffer preserved, and the external DS is
one way I've done it in the past. If the file is defined with a prefix option it
facilitates working with "real" field names in the DS. The reason I find this
method a tad inelegant is the double move required; first the system moves it,
then I move it again. It does work well though.

Micheal, I must have missed the SPECIAL file boat. Sounds intriguing, can you
elaborate?

I thought about this method, have not tried it yet. In meta code:

select DISTINCT cust as custL1 from invoices
do while not eof
 select * from invoices where cust = custL1
  do while not eof
    ...detail processing
    read next
  end while
  ...total processing
  read next
end while

If this even works it probably trades in performance for elegance (or cleverness).

I also am still using the cycle where I feel it is useful.
The SQL can be put in a separate program and used as a SPECIAL FILE program 
with the cycle so that you can code level breaks !!

I realize that I'm a cycle addict, and that there are probably any number of
reasons why you couldn't do it, but without seeing the code I would be sorely
tempted to see if there was a way one could run the SQL output into a file and
use the cycle. There is something to be said for elegance.

I guess it all depends on what type of processing you are going to be doing at 
level-break time...do you need the data from the last record in the record 
buffer or not.

Of course you CAN still use the cycle if you want, but if you want to control 
everything in your program and simulate the cycle then you can create an 
external data structure in your program and have ALL processing over the DS 
field names.

In you main program loop you would read your file and check for level breaks.  
Do all level break processing then move the data from the file into your 
external DS and continue with detail processing.  This way you ONLY move new 
data into your external DS after all level-break processing has completed.

You don't even have to do anything that fancy if all you are doing is 
processing totals or something that doesn't require all the detail data from 
the last record.

Lets say I'm inside a do-while group reading an SQL cursor, the underlying data
contains invoices for several customers, it's ordered by customer and invoice
number.

I need to run some code at the end of each customer's invoices, problem is the
only way I know when I'm at "total time" is when I've already moved to the next
customer.

Back in the old days, the RPG cycle would take care of this nuisance quite
nicely. I'm wondering how others handle the situation without the cycle?

I can imagine several different ways myself, none of them strike me as elegant
by any means, perhaps because I've been a cycle-addict for so long.