[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

return N records from table with 1 to many join

Posted on 2008-01-28
20
Medium Priority
?
805 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:rd_kellerman
  • 9
  • 7
  • 3
  • +1
20 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 20763383


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

Expert Comment

by:sventhan
ID: 20763398
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
 

Author Comment

by:rd_kellerman
ID: 20763457
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 18

Expert Comment

by:sventhan
ID: 20763502
select orderdetail.modelnum,sum(orderdetail.qtyallocated) qa
from orderdetail,serialtrack
where orderdetail.modelnum = serialtrack.modelnum
and serialtrack.state = 1
group by orderdetail.modelnum
0
 
LVL 18

Expert Comment

by:sventhan
ID: 20763512
I missed the SUM, try the above and that will work
0
 

Author Comment

by:rd_kellerman
ID: 20763615
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
 
LVL 18

Expert Comment

by:sventhan
ID: 20763662
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
 

Author Comment

by:rd_kellerman
ID: 20763840
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
 
LVL 18

Expert Comment

by:sventhan
ID: 20764280
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
 
LVL 26

Expert Comment

by:ee_rlee
ID: 20765538
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
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20765787
>> EXCEPT the qa column looks like it is not filtered by the orderkey= '000016572'

Wat is the query u tried for that ?
0
 

Author Comment

by:rd_kellerman
ID: 20768277
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
 

Author Comment

by:rd_kellerman
ID: 20768597
To ee rlee,
When I run your query I get the same results as previously posted.

Thanks,
RDK
0
 

Author Comment

by:rd_kellerman
ID: 20768747
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
 
LVL 18

Expert Comment

by:sventhan
ID: 20772210
Thanks RDK.

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

Sve.
0
 

Author Comment

by:rd_kellerman
ID: 20772443
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
 
LVL 26

Expert Comment

by:ee_rlee
ID: 20774374
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
 

Author Comment

by:rd_kellerman
ID: 20792081
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
 
LVL 26

Accepted Solution

by:
ee_rlee earned 2000 total points
ID: 20793460
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
 

Author Closing Comment

by:rd_kellerman
ID: 31425784
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question