Looking for Ideas - how would you allow users to indicate a report break?

[MelissaG]

We have a process where the users define how they want data to sort and which fields they want to "break" on. We're building carts and slots. So, if they're going to sort by load date, truck, and color and want to break on the truck number, I start a new cart every time I hit a new truck. If they want to break on truck number and color, I start a new cart if either field is different.

So, I have a dynamic SQL in the sense that I use their choices to build the "SORT" statement - and everything up to this point seems to be working as planned.

Now, I'm stuck. Technically, they could have up to 99 fields to break on. I can find out which ones they've marked, but then what?

I know I can't do something "easy" like pulling the field name. I'm not very familiar with using pointers, so I don't know if that would help me or not.

So, I'm doing a FETCH on the data (sorted as they wish), I need to keep the data from the fields they want to break on so I can test it after the next FETCH, but how the heck do I know which fields those are? I can save the field names in a table, but the field names themselves don't seem to help me much.

I think I just need a push in the right direction here. Any ideas will be greatly appreciated.

[TedHolt]

Since you're building the SELECT statement dynamically, you could add some break field columns to the result set and load them from the selected fields. Define the break fields as character and use the CHAR function to convert numeric fields. Make your RPG program check the break fields for a change.

Here's a query with one break field:

select cm.CUSNUM, cm.LSTNAM, cm.CITY, cm.STATE,
cm.CDTLMT, cm.CHGCOD, cm.BALDUE, cm.CDTDUE,
cm.STATE as BreakField_1,
' ' as BreakField_2
from qiws.qcustcdt as cm
order by cm.State;

Here's a query with two break fields:

select cm.CUSNUM, cm.LSTNAM, cm.CITY, cm.STATE,
cm.CDTLMT, cm.CHGCOD, cm.BALDUE, cm.CDTDUE,
cm.STATE as BreakField_1,
char(cm.CDTDUE) as BreakField_2
from qiws.qcustcdt as cm
order by cm.State, cm.CDTDUE;

[MelissaG]

Thanks, Ted. I've gotten close to what they want. I've also looked at some old posts and realized that I may not be able to do what the business wants. I really could use a dynamic "INTO" (Don't worry; I'm not going to go there. Ha, ha)

got my head wrapped around it with the help of a COBOL programmer - nothing like going old school