return N records from table with 1 to many join

select orderdetail.modelnum,(orderdetail.qtyallocated)
from orderdetail,serialtrack
where orderdetail.modelnum = serialtrack.modelnum
group by orderdetail.modelnum

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

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

select orderdetail.modelnum,sum(orderdetail.qtyallocated) qa
from orderdetail,serialtrack
where orderdetail.modelnum = serialtrack.modelnum
and serialtrack.state = 1
group by orderdetail.modelnum

I missed the SUM, try the above and that will work

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

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...

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.

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.

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

Select allOpen in new window

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

Wat is the query u tried for that ?

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

To ee rlee,
When I run your query I get the same results as previously posted.

Thanks,
RDK

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

Thanks RDK.

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

Sve.

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

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;

Select allOpen in new window

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

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