FB-3 Sql Loop

[henry...@hotmail.com]

Good Day,

Location Table :

ITEMNO  LOCNO   QTY

 BB             A1           10

 BB             A2           20

 BB             A3           30

 BB             A4           10

 CC             C5           12 

If i need to pick 10 pieces BB Item from locations the result should be 

ITEMNO  LOCNO   QTY

 BB             A1           10

If i need to pick 64 pieces BB Item from locations the result should be 

ITEMNO  LOCNO   QTY

 BB             A1           10

 BB             A2           20

 BB             A3           30

 BB             A4             4

How can i do that ?

Thank You

 

[Tomasz Tyrakowski]

You didn't say which version of Firebird you use. In FB 3.0+, the
following query with a window function will do the job (the example is
for 'BB' and 64):

with CumulativeLoc as (
select ITEMNO, LOCNO, QTY,
sum(QTY) over (partition by ITEMNO order by LOCNO) as CUMQTY
from LocationTable
)
select
ITEMNO, LOCNO,
iif(64 > CUMQTY, QTY, QTY - (CUMQTY - 64)) as QTY
from CumulativeLoc
where
ITEMNO = 'BB'
and 64 >= CUMQTY - QTY

In Firebird < 3.0, you'd probably need a stored procedure, iterating
over the rows and counting how many items you've already taken.

regards
Tomasz

[Tomasz Tyrakowski]

On 08.06.2025 at 10:25, Tomasz Tyrakowski wrote:
> On 07.06.2025 at 12:39, henry...@hotmail.com wrote:

>> [...]

> with CumulativeLoc as (
>     select ITEMNO, LOCNO, QTY,
>     sum(QTY) over (partition by ITEMNO order by LOCNO) as CUMQTY
>     from LocationTable

A small self-correction: I'd add

order by LOCNO

here, as window function doesn't ensure the order of the returning rows
(only the order in which the cumulative sum is calculated) and the order
is important here.

> )
> select
>     ITEMNO, LOCNO,
>     iif(64 > CUMQTY, QTY, QTY - (CUMQTY - 64)) as QTY
> from CumulativeLoc
> where
>     ITEMNO = 'BB'
>     and 64 >= CUMQTY - QTY
>

regards
Tomasz

[henry...@hotmail.com]

Thank you so much Tomasz