Re: SQL how to question

Starting with Charles' suggestion ... you can get to this:

-----------------
with numbered
as (select customer, ordernr, row_number() OVER (partition by customer
order by ordernr)
as rowNbr
, dense_rank() OVER (order by customer) grpNbr
from qtemp.orders)
select customer, ordernr,
dense_rank() OVER (order by grpNbr, ceiling(rownbr / 5.00)) as Dossier,
row_number() OVER (partition by grpNbr, ceiling(rownbr / 5.00) order by
grpNbr, ceiling(rownbr / 5.00)) as DossierSeq
from numbered;
----

From this example, it seems to work:

CREATE or replace TABLE qtemp.orders (
"ID" INTEGER GENERATED ALWAYS AS IDENTITY (
START WITH 1 INCREMENT BY 1
NO MINVALUE NO MAXVALUE
NO CYCLE NO ORDER
CACHE 20 )
,
customer CHAR(15) CCSID 280 NOT NULL DEFAULT '' ,
ordernr char(15) CCSID 280 NOT NULL DEFAULT '' ,
TS_INSERT TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
TS_UPDATE TIMESTAMP GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE
TIMESTAMP NOT NULL
)
;
insert into qtemp.orders (customer, ordernr)
values('A', 'ordA01'),
('A', 'ordA02'),
('A', 'ordA03'),
('B', 'ordB01'),
('B', 'ordB02'),
('B', 'ordB03'),
('B', 'ordB04'),
('B', 'ordB05'),
('B', 'ordB06'),
('C', 'ordC01'),
('D', 'ordD01'),
('D', 'ordD02'),
('D', 'ordD03'),
('D', 'ordD04'),
('D', 'ordD05'),
('D', 'ordD06'),
('E', 'ordE01');

HTH
Roberto



Il giorno gio 15 apr 2021 alle ore 22:30 Charles Wilt <
charles.wilt@xxxxxxxxx> ha scritto:

Here's a (partial) solution...

with numbered
as (select clnt, ord, row_number() OVER (partition by clnt order by ord)
as rowNbr
, dense_rank() OVER (order by clnt) grpNbr
,
from mytest)
select clnt, ord
, grpNbr + rownbr / 5 as dossier
, mod(rownbr - 1, 5) + 1 as order
from numbered;

But the question is what happens for "Client C"? I suspect that would be a
dossier of 4...which the above wouldn't support.

May need a UDF that makes use of the scratchpad..

Or if on 7.3, make use of the LAG() function

Charles






On Thu, Apr 15, 2021 at 8:58 AM Dave <dfx1@xxxxxxxxxxxxxx> wrote:

Hi,

I was thinking I could do this with an OLAP function, but there's a
particularity that has me stumped

How can I get from this :
Client A Order x
Client A Order z
Client A Order a
Client B Order g
Client B Order h
Client B Order i
Client B Order l
Client B Order p
Client B Order m

To this:
Client A Order x 1 1
Client A Order z 1 2
Client A Order a 1 3
Client B Order g 2 1
Client B Order h 2 2
Client B Order i 2 3
Client B Order l 2 4
Client B Order p 2 5
Client B Order m 3 1

A dossier n° is assigned to each client and a line number to each order.
If there are more than 5 orders, a new dossier is created.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

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

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com