Greg,
Sorry if I added any confusion to the topic by my last post. It looks like
you may have been responding to Rob Berendt and not Rob Williams.
Rob W.
------------------------------
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Greg
Wilburn
Sent: Tuesday, August 30, 2022 3:12 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQL Question
CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know the
content is safe.
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
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob
Berendt
Sent: Monday, August 29, 2022 9:52 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQL Question
Sort of like this?
create table rob.cartonarrangement (
cartonarrangementid integer,
carton varchar(30),
itemnumber varchar(30),
cartonquantity integer
);
drop table rob.cartonarrangement;
insert into rob.cartonarrangement values(1, 'boxA', 'widget1', 3); insert
into rob.cartonarrangement values(1, 'boxA', 'widget2', 1); insert into
rob.cartonarrangement values(1, 'boxA', 'widget3', 1); insert into
rob.cartonarrangement values(2, 'boxB', 'widget2', 1); insert into
rob.cartonarrangement values(2, 'boxB', 'widget3', 1); create table
rob.selection (
itemnumber varchar(30),
cartonquantity integer
);
insert into rob.selection values('widget2', 1); insert into rob.selection
values('widget3', 1);
set schema = 'ROB';
With t1 as (
select cartonarrangementid, count(*) as rowcount
from cartonarrangement
group by cartonarrangementid)
select * from t1;
With t1 as (
select cartonarrangementid, count(*) as rowcount
from cartonarrangement
group by cartonarrangementid),
T2 as (
select count(*) as rowcount
from selection),
T3 as (
select *
from cartonarrangement
where itemnumber concat char(cartonquantity) in (
select itemnumber concat char(cartonquantity) from selection))
select t3.*, b.rowcount, c.rowcount
from t3,
lateral (select t1.rowcount from t1
where t1.cartonarrangementid = t3.cartonarrangementid) b, lateral
(select t2.rowcount from t2) c where b.rowcount = c.rowcount ;
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Greg
Wilburn
Sent: Friday, August 26, 2022 4:57 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL Question
CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know the
content is safe.
We have a historical table containing what items we previous packed into
different boxes. I'd like to draw on that historical data to extrapolate
what box to use when packing the same items/qty again.
BOX TYPE, QTY, SKU
Data might look like this
boxA, 3, widget1
boxA, 1, widget2
boxA, 1, widget3
boxB, 1, widget2
boxB, 1, widget3
And so forth. This means that "boxA" can hold 3 of widget1 and 1 each of
widget2 and widget3
I would like to query the table to so what box type to use when I have 1 of
widget2 and 1 of widget3 (i.e. find "boxB"). In other words, find a
"matching" box type for my current two rows.
1 widget2
1 widget3
Is this even possible with SQL?
[Logo]<
https://www.totalbizfulfillment.com/> Greg Wilburn
Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
1 Corporate Dr
Grantsville, MD 21536
www.totalbizfulfillment.com<
http://www.totalbizfulfillment.com>
midrange.com
