RE: SQL limit with fetch first together with total amount limit

It can be done with a cursor and a DO ... WHILE loop in a complex SQL statement.

This example is from the DB2 V9 infocenter page. It doesn't have a cursor, but shows how to sum until a condition exists. Theres a specific form of DECLARE for declaring a cursor. You can update through a cursor.

CREATE PROCEDURE sum_mn (IN p_start INT
,IN p_end INT
,OUT p_sum INT)
SPECIFIC sum_mn
LANGUAGE SQL
smn: BEGIN

DECLARE v_temp INTEGER DEFAULT 0;
DECLARE v_current INTEGER;

SET v_current = p_start;

WHILE (v_current <= p_end) DO
SET v_temp = v_temp + v_current;
SET v_current = v_current + 1;
END WHILE;
p_sum = v_current;
END smn;

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Monday, January 10, 2011 10:25 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL limit with fetch first together with total amount limit

Definitely not a good application for SQL.

You can do it. You need to create a view with every unique key and the total of records less than or equal to that key. Then you need to use a second select to find the key where the total is just less than what you need. Then you need to process all records whose key is less than that. However, this requires massive amounts of processing.

By far the better tool for this job is ISAM (otherwise know as "traditional" RPG file I/O).

Joe

Hi Joe,

If there are 150.000 records with status A with a total sum of the
EUR amounts 75.000, then the requirement is to select up to a maximum
of 50.000 record, with the added condition that the total EUR sum does
not exceed 1.000,00.

So, if the sum of the EUR amounts is reached within the first 250
records than that is the limit in the number of records to select.

This is where I do not see how to do that.

Kind regards,

Loek Maartens.


"Joe Pluta"<joepluta@xxxxxxxxxxxxxxxxx>
wrote in message news:4D2B89A6.4080600@xxxxxxxxxxxxxxxxx...

The generalized approach to this is as follows:

update MYTABLE set field1 = value1 where (keyfields) in
(select keyfields from MYTABLE
group by keyfields having [summary conditions])

With additional selection criteria, it would be:

update MYTABLE set field1 = value1 where (keyfields) in
(select keyfields from MYTABLE
where [selection conditions]
group by keyfields having [summary conditions])
and [selection conditions]

This would total each combination of the key fields and then update
the sets that match your summary conditions. However, your case
isn't the same, because you seem to be limiting the selection based
on the key fields (you only want to update AA/EUR records, and only
if the sum of the AA/EUR records meets the conditions).

In your case, it seems you just want to select the totals into some
work fields, and if they match your conditions go back and update the
records with a second UPDATE.

Joe

Hi group,

I have to update the status field of a group of records where:
A: The status is 'AA'
and B: The currency code is 'EUR'
and C: The total amount of the selection is less than 1000,00 and D:
The total number of records selected must be less or equal to
50.000.

Record fields: Recid, Status, Curcode, Amount, Description

Total number of records is 1.000.000

I know that I can limit the selection with FETCH FIRST, and
specifying the correct status code and currency code can be embeded
within the select clause, but I fail to see how I can limit the
selection with a total amount as a limit for the selexcted records.

Can anyone show me how to aproach this?

Kind regards,

Loek Maartens.

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

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