return N records from table with 1 to many join

Help,
Here is my scenero. I have two tables the first is called orderdetail, it has columns such as modelnum, qtyallocated and more... The second table is called serialtrack, it has columns such as modelnum, serialnum etc. The orderdetail table can have a modelnum listed on multiple lines with a qtyallocated for each line (modelnum).  The serialtrack table will have lots (500k or more) of serialnum's for each modelnum where orderdetail.modelnum = serialtrack.modelnum.  What I need to do is to return only the SUM of orderdetail.qtyallocated(N)  number of serialnum's from serialtrack table. The only link from orderdetail to serialtrack is the modelnum.  The other requirement is that a field called state in serialtrack is = 1(available).  i am sure this is not a hard as it seems to me because I'm not very experienced in SQL.

Thanks,
RDK
rd_kellermanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sventhanCommented:


select orderdetail.modelnum,(orderdetail.qtyallocated)
from orderdetail,serialtrack
where orderdetail.modelnum = serialtrack.modelnum
group by orderdetail.modelnum
0
sventhanCommented:
hopefully this will work

select orderdetail.modelnum,(orderdetail.qtyallocated)
from orderdetail,serialtrack
where orderdetail.modelnum = serialtrack.modelnum
and serialtrack.state = 1
group by orderdetail.modelnum
0
rd_kellermanAuthor Commented:
When I run this from SQL+ I get
 1) select orderdetail.sku, (orderdetail.qtyallocated)
                                           *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

PS. I used modelnum in the question instead of sku, so modelnum is really the sku field.

Thanks,
RDK
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sventhanCommented:
select orderdetail.modelnum,sum(orderdetail.qtyallocated) qa
from orderdetail,serialtrack
where orderdetail.modelnum = serialtrack.modelnum
and serialtrack.state = 1
group by orderdetail.modelnum
0
sventhanCommented:
I missed the SUM, try the above and that will work
0
rd_kellermanAuthor Commented:
It ran OK. I just need to try narrowing it down by selecting by orderdetail.orderkey = '000001653' (this will probably be a parameter field input at runtime) . The other thing i noticed was a lot of '0' values in the QA field.  I guess I need to make sure the sum of qtyallocated is > 0.

Thanks,
RDK
0
sventhanCommented:
select orderdetail.modelnum,sum(orderdetail.qtyallocated) qa
from orderdetail,serialtrack
where orderdetail.modelnum = serialtrack.modelnum
and serialtrack.state = 1
and orderdetail.orderkey = '000001653'
and orderdetail.qtyallocated > 0
group by orderdetail.modelnum

you could replace '000001653' with the input parameter...


0
rd_kellermanAuthor Commented:
The results from SQL + are:
SKU                          QA
-------------------- ----------
2TXCB036BC3HCA             9504
2TXCB042BC3HCA             1521
2TXCC060BC3HCA             1108
4TXCB036BC3HCA             3552
4TXCB042BC3HCA              152
4TXCC037BC3HCA              114
4TXCC049BC3HCA              412
4TXCC060BC3HCA             1833
TUD060R9V3K                3736
TUD080R9V3K               14370
TUD080R9V4K                7080

SKU                          QA
-------------------- ----------
TUD100R9V5K               15030
TUD1C100A9601A             1848
TUD1D120A9H51B               56
TUE1A040A9241A              998
TUX1B060A9361A             1416
TUX1B080A9421A              386
TUX1C080A9601A              117
TUX1D120A9601A              169

This is close as far as type of results that I expect - EXCEPT the qa column looks like it is not filtered by the orderkey= '000016572' The Quantity (SUM) should be like 16 for the  2TXCB036BC3HCA model (first return field) - so it looks like the sum is from ALL orders not the filtered sum of qtyallocated.
0
sventhanCommented:
please desc both the tables with all the possible columns that you want to use in the sql query.
I just wud like to know the datatype of those columns.
0
ee_rleeCommented:
hi

try this
select orderdetail.modelnum,sum(to_number(orderdetail.qtyallocated)) qa
from orderdetail,serialtrack
where orderdetail.modelnum = serialtrack.modelnum
and serialtrack.state = 1
and orderdetail.orderkey = '000001653' 
group by orderdetail.modelnum
having sum(to_number(orderdetail.qtyallocated)) > 0

Open in new window

0
Jinesh KamdarCommented:
>> EXCEPT the qa column looks like it is not filtered by the orderkey= '000016572'

Wat is the query u tried for that ?
0
rd_kellermanAuthor Commented:
jinesh,
here is the query:

select orderdetail.sku,
sum(orderdetail.qtyallocated) qa
from orderdetail,serialtrack
where orderdetail.sku = serialtrack.sku
AND orderdetail.orderkey = '0000016572'
AND serialtrack.state = 1
group by orderdetail.sku;

Thanks,
RDK
0
rd_kellermanAuthor Commented:
To ee rlee,
When I run your query I get the same results as previously posted.

Thanks,
RDK
0
rd_kellermanAuthor Commented:
Sventhan,
Here are the fields:
From OrderDetail
ORDERKEY - VARCHAR2(10BYTE) i.e. orderdetail.orderkey = '0000016572
SKU (OR MODELNUM) - VARCHAR2(20BYTE) i.e. '2TXCB036BC3HCA' (links to serialtrack.sku). Sometimes there is more than 1 row with the same sku - hence it needs to be grouped and qtyallocated summed for each sku group.
QTYALLOCATED - NUMBER(10) i.e 6, 9, ...

From SerialTrack
SKU - VARCHAR2(20BYTE) i.e. '2TXCB036BC3HCA' (MANY SIDE OF LINK)
STATE - VARCHAR2(20BYTE) i.e. where '1' is available
SERIALNUM - VARCHAR2(20BYTE) This is the field I need to return N rows where N is the sum of sku qtyallocated group for all the different sku's in orderdetail.

Also I think there may be a table or view that has all the sku's in an order listed on it's own row with a qty of 1, would that be simpler? Like if there are 100 qtyallocated for the order there would be 100 rows of sku's (though not unique) with qtyallocated of 1 in this table.  I think the problem with this would be returning the next serialnum from serialtrack for each line as opposed to using a sum and grabbing that number of serialnums for that sku.
Thanks,
RDK
0
sventhanCommented:
Thanks RDK.

Can you post a sample output, which you want to get it from the SQL?

Sve.
0
rd_kellermanAuthor Commented:
OK Sve,
With some help in the office, here's SQL I am testing now.

SELECT
o.orderkey, o.sku, o.qtyallocated AS qty, s.serialnum
FROM orderdetail o
INNER JOIN
(SELECT row_number() over(partition by s.sku order by s.serialnum) as rownumber,
s.sku, s.serialnum, s.state FROM serialtrack s
WHERE s.state = '1') s on o.sku = s.sku
WHERE orderkey = '0000016572'
AND s.rownumber <= o.qtyallocated;

Here is what I get running this in SQL+

ORDERKEY   SKU                         QTY SERIALNUM
---------- -------------------- ---------- ---------
0000016572 2TXCB036BC3HCA                7 7023KGR5G
0000016572 2TXCB036BC3HCA                9 7023KGR5G
0000016572 2TXCB036BC3HCA                7 7026WLE5G
0000016572 2TXCB036BC3HCA                9 7026WLE5G
0000016572 2TXCB036BC3HCA                7 7026YXN5G
0000016572 2TXCB036BC3HCA                9 7026YXN5G
0000016572 2TXCB036BC3HCA                7 7043C3S5G
0000016572 2TXCB036BC3HCA                9 7043C3S5G
0000016572 2TXCB036BC3HCA                7 7043C8F5G
0000016572 2TXCB036BC3HCA                9 7043C8F5G
more rows .....

This is SO close, but you notice that the serialnum field from serialtrack tbl repeats twice (or however many order lines have the same sku - I think) for each sku in orderdetail tbl.  The sku line repeats 16 times which is the total of sum of qtyallocated 9 + 7 here - which is the correct number of rows but serialnum's must be unique. One for each orderline.  The sku '2TXCB036BC3HCA' has two lines in orderdetail table; the first row with qtyallocated of 7 and the second row has qtyallocated of 9. I need 16 unique serial numbers for this sku. Hope I not confusing you with the long description.

Rdk

0
ee_rleeCommented:
try this
SELECT
o.orderkey, o.sku, o.qty, s.serialnum
 
(SELECT t.orderkey, t.sku, SUM(t.qtyallocated) AS qty
FROM orderdetail t
WHERE t.orderkey = '0000016572'
GROUP BY t.orderkey, t.sku) o
 
INNER JOIN
 
(SELECT row_number() over(partition by s.sku order by s.serialnum) as rownumber,
s.sku, s.serialnum, s.state FROM serialtrack s
WHERE s.state = '1') s on o.sku = s.sku
 
WHERE s.rownumber <= o.qtyallocated;

Open in new window

0
rd_kellermanAuthor Commented:
ee
Here are the SQL+ results from your last post:

SQL> SELECT
  2  o.orderkey, o.sku, o.qty, s.serialnum, (< I added this comma - but tried it both ways to be sure)
  3  (SELECT t.orderkey, t.sku, SUM(t.qtyallocated) AS qty
  4  FROM orderdetail t
  5  WHERE t.orderkey = '0000016572'
  6  GROUP BY t.orderkey, t.sku) o
  7  INNER JOIN
  8  (SELECT row_number() over(partition by s.sku order by s.serialnum) as rownumber,
  9  s.sku, s.serialnum, s.state FROM serialtrack s
 10  WHERE s.state = '1') s on o.sku = s.sku
 11  WHERE s.rownumber <= o.qtyallocated;
INNER JOIN
*
ERROR at line 7:
ORA-00923: FROM keyword not found where expected

I am doing this at work, so I will check post from around 8:00 AM (CST) to 5:00 PM

Thanks
rdk
0
ee_rleeCommented:
sorry should be like this, pls try again
SELECT
o.orderkey, o.sku, o.qty, s.serialnum
 
FROM
(SELECT t.orderkey, t.sku, SUM(t.qtyallocated) AS qty
FROM orderdetail t
WHERE t.orderkey = '0000016572'
GROUP BY t.orderkey, t.sku) o
 
INNER JOIN
 
(SELECT row_number() over(partition by s.sku order by s.serialnum) as rownumber,
s.sku, s.serialnum, s.state FROM serialtrack s
WHERE s.state = '1') s on o.sku = s.sku
 
WHERE s.rownumber <= o.qtyallocated;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rd_kellermanAuthor Commented:
ee
This is it! the only thing I changed in your last post was the last line 'WHERE s.rownumber <= o.qtyallocated;' - I changed o.qtyallocated to o.qty. I want to Really THANK ALL who helped with this, you guys are great!  
Thanks much ee
rdk
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.