RE: SQL Question

Hi Greg,

I would recommend you test it by using my sample table and not making any
modifications.

Delete the comment indicators from the CREATE TABLE, DELETE, and INSERT
lines them compile it with a statement similar to the following.

RUNSQLSTM SRCFILE(QRPGLESRC)
SRCMBR(BOX)
COMMIT(*NONE)
ERRLVL(10)
MARGINS(100)
DFTRDBCOL(ROB)

Insert your sample rows for (1 of widget1, 1 of widget2, 2 of widget3).

INSERT INTO BOX_HISTORY
(order_nbr, box_type, qty, sku)
VALUES (5, 'boxZ', 1, 'widget1'),
(5, 'boxZ', 1, 'widget2'),
(5, 'boxZ', 2, 'widget3');

And test it by calling
call get_box_type ('widget1', 1, 'widget2', 1, 'widget3', 2);

I get the following result
boxZ 1 widget1 1 widget2 2
widget3

Rob

------------------------------

message: 3
date: Tue, 30 Aug 2022 19:11:34 +0000
from: Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
subject: RE: SQL Question

Rob,

Thanks for this. I adapted this using our actual tables... Assuming I did
it correctly (big assumption) the result set contains boxes that contain one
of the items/qty.

I might be going down the wrong path here anyway.

What I'm really trying to do is this:

Given an order containing
1 of widget1
1 of widget2
2 of widget3

Knowing the dimensions of each item, I would like to determine what shipping
carton to use.
I was hoping I could my historical data to extrapolate the answer... but
that may be the wrong approach.

Would be nice to call some software that would do that for you.

Thx,
Greg