Solved

Merging output from two one to many sub-queries

Posted on 2014-07-24
8
399 Views
Last Modified: 2014-07-25
I have 3 tables (Oracle 11g R2).

Stock Code (details about the stock, stock code, description, price, etc)
Parts List (a pre defined list of parts needed for a job.  An oil filter could be in multiple lists with different other components)
Part No    (each stock code can have multiple part numbers, theoretically if the same part could be bought from 2 different makers)

Code for these is below as well as some data:

CREATE TABLE STOCK_CODE      (STOCK_CODE CHAR(9), STOCK_NAME CHAR(40), STOCK_PRICE NUMBER(13,2));
CREATE TABLE DEMO.PARTS_LIST (LIST_ID CHAR(10), STOCK_CODE CHAR(9)); 
CREATE TABLE DEMO.PART_NO    (MANUFACTURER CHAR(10), PART_NO CHAR(20), STOCK_CODE CHAR(9));

insert into stock_code values ('000000001', 'Oil Filter', 20.00);
insert into stock_code values ('000000002', 'part 2', 10.00);
insert into stock_code values ('000000003', 'part 3', 30.00);
insert into stock_code values ('000000004', 'part 4', 40.00);

insert into Parts_list values ('A1', '000000001');
insert into Parts_list values ('A2', '000000001');
insert into Parts_list values ('A3', '000000001');
insert into Parts_list values ('B1', '000000003');
insert into Parts_list values ('B2', '000000003');
insert into Parts_list values ('C1', '000000004');

insert into Part_no values ('MANUF1', 'OILFILT1', '000000001');
insert into Part_no values ('MANUF2', 'OILFILT2', '000000001');
insert into Part_no values ('MANU1', 'PART1', '000000003');
insert into Part_no values ('MANU3', 'PART3', '000000003');
insert into Part_no values ('MANU2', 'PART2', '000000003');
insert into Part_no values ('MANU4', 'PART4', '000000004');

Open in new window


For each stock code I need each part reported once, and each manufacturer / part no reported once

The four scenarios I can see are:

a) More lists than parts (stock code 1)
b) more parts than lists (stock code 3)
c) same number of lists and parts (stock code 4)
d) none of either (stock code 2)

I put together this query, thinking I could use the row number for each part to get me some output:

select a.stock_code, a.stock_name, a.stock_price, nvl(a.list_id, ' ') as list_id, nvl(b.manufacturer, ' ') as manufacturer, 
       nvl(b.part_no, ' ') as part_no
from
(
 select row_number() over (partition by w.stock_code order by w.stock_code) as row_num,
 w.stock_code, w.stock_name, w.stock_price, x.list_id
 from stock_code w
 left join parts_list x on w.stock_code = x.stock_code
) a
full outer join
(
 select row_number() over (partition by y.stock_code order by y.stock_code) as row_num,
 y.stock_code, z.manufacturer, z.part_no
 from stock_code y
 left join part_no z on y.stock_code = z.stock_code
) b
on a.row_num = b.row_num and a.stock_code = b.stock_code
order by a.stock_code, b.manufacturer, b.part_no
/

STOCK_COD STOCK_NAME                               STOCK_PRICE LIST_ID    MANUFACTUR PART_NO
--------- ---------------------------------------- ----------- ---------- ---------- --------------------
000000001 Oil Filter                                        20 A2         MANUF1     OILFILT1
000000001 Oil Filter                                        20 A1         MANUF2     OILFILT2
000000001 Oil Filter                                        20 A3
000000002 part 2                                            10
000000003 part 3                                            30 B1         MANU1      PART1
000000003 part 3                                            30 B2         MANU2      PART2
000000004 part 4                                            40 C1         MANU4      PART4
                                                                          MANU3      PART3

Open in new window


I am very close but note that last line has no information about the stock code present.   Because there is no third row in lists, it can't match the stock code.

Expected output should be:

STOCK_COD STOCK_NAME                               STOCK_PRICE LIST_ID    MANUFACTUR PART_NO
--------- ---------------------------------------- ----------- ---------- ---------- --------------------
000000001 Oil Filter                                        20 A2         MANUF1     OILFILT1
000000001 Oil Filter                                        20 A1         MANUF2     OILFILT2
000000001 Oil Filter                                        20 A3
000000002 part 2                                            10
000000003 part 3                                            30 B1         MANU1      PART1
000000003 part 3                                            30 B2         MANU2      PART2
000000003 part 3                                            30            MANU3      PART3
000000004 part 4                                            40 C1         MANU4      PART4

Open in new window


Can anyone please help me with the piece I'm missing here ?

Thanks
Steve
0
Comment
Question by:Steve Wales
  • 4
  • 3
8 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 400 total points
ID: 40218447
Yes, it is possible with an additional full join, like this:
select NVL(a.stock_code, c.stock_code) stock_code, 
       NVL(a.stock_name, c.stock_name) stock_name, 
       NVL(a.stock_price, c.stock_price) as stock_price, 
       nvl(a.list_id, ' ') as list_id, nvl(b.manufacturer, ' ') as manufacturer, 
       nvl(b.part_no, ' ') as part_no
from
(
 select row_number() over (partition by w.stock_code order by w.stock_code) as row_num,
 w.stock_code, w.stock_name, w.stock_price, x.list_id
 from stock_code w
 left join parts_list x on w.stock_code = x.stock_code
) a
full join 
(
  SELECT z.stock_code,
  row_number() over (partition by z.stock_code order by z.stock_code) as row_num,
  w.stock_name, w.stock_price
  from stock_code w
  left outer join part_no z on w.stock_code = z.stock_code
) c on a.stock_code = c.stock_code and a.row_num = c.row_num
full outer join
(
 select row_number() over (partition by y.stock_code order by y.stock_code) as row_num,
 y.stock_code, z.manufacturer, z.part_no
 from stock_code y
 left join part_no z on y.stock_code = z.stock_code
) b
on NVL(a.row_num, c.row_num) = b.row_num and NVL(a.stock_code, c.stock_code) = b.stock_code
order by a.stock_code, b.manufacturer, b.part_no

Open in new window

Result:
| STOCK_CODE |                               STOCK_NAME | STOCK_PRICE |    LIST_ID | MANUFACTURER |              PART_NO |
|------------|------------------------------------------|-------------|------------|--------------|----------------------|
|  000000001 | Oil Filter                               |          20 | A2         |   MANUF1     | OILFILT1             |
|  000000001 | Oil Filter                               |          20 | A1         |   MANUF2     | OILFILT2             |
|  000000001 | Oil Filter                               |          20 | A3         |              |                      |
|  000000002 | part 2                                   |          10 |            |              |                      |
|  000000003 | part 3                                   |          30 | B1         |   MANU1      | PART1                |
|  000000003 | part 3                                   |          30 | B2         |   MANU2      | PART2                |
|  000000004 | part 4                                   |          40 | C1         |   MANU4      | PART4                |
|  000000003 | part 3                                   |          30 |            |   MANU3      | PART3                |
|     (null) | part 2                                   |          10 |            |              |                      |
		

Open in new window

SQLFiffle
0
 
LVL 22

Author Comment

by:Steve Wales
ID: 40218467
OK I'll try it out against my live data and let you know.  I can exclude that last row myself with extra where clause.

Thanks!
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 40219741
I started thinking about this last night and thought that there has to be a cleaner way to do this but couldn't think of it.

I really still can't think of it but came up with a kludge.

The caveat is you either need to hard-code a maximum number of possible entries in the parts tables or grab it from an outside query and store it in a variable.

I cannot get a subquery to work...

The concept I came up with is to generate is to generate DUMMY rows for the maximum 'allowed' then a simple join.

I borrowed the making up of extra rows from:
https://community.oracle.com/thread/861747

Here is what I ended up with:
var max_needed number

--get the maximum count of stock_code entries from both the parts_list and part_no tables
begin
select max(mycount)
into :max_needed
from
(
select max(count(*)) mycount from parts_list group by stock_code
union all
select max(count(*)) from part_no group by stock_code
);
end;
/


select s.stock_code, s.stock_name, s.stock_price, m.list_id, m.manufacturer, m.part_no
from stock_code s
left outer join
(
	select a.stock_code, a.list_id, b.manufacturer, b.part_no from
	(
	select stock_code, list_id, rn
  	from parts_list
 	model
 	partition by(stock_code)
 	dimension by(Row_Number() over(partition by stock_code order by list_id) as rn)
 	measures(list_id)
 	rules(list_id[for rn from 1 to :max_needed INCREMENT 1] 
     	= presentv(list_id[cv()],list_id[cv()],null))
	) a
	join
	(
	select stock_code, manufacturer, part_no, rn
  	from part_no
 	model
 	partition by(stock_code)
 	dimension by(Row_Number() over(partition by stock_code order by manufacturer) as rn)
 	measures(manufacturer, part_no)
 	rules(manufacturer[for rn from 1 to :max_needed INCREMENT 1] 
     	= presentv(manufacturer[cv()],manufacturer[cv()],null))
	) b
	on a.stock_code=b.stock_code and a.rn=b.rn
	where coalesce(list_id,manufacturer,part_no) is not null
) m
on s.stock_code=m.stock_code
order by stock_code, list_id, manufacturer
/

Open in new window

0
 
LVL 22

Author Comment

by:Steve Wales
ID: 40219771
It's kinda messy any way you look at it really :)

It's a one off kind of extract for a data conversion from one ERP system to another so that the inventory controllers can analyze what they have.  Finding a max number of rows is easy - query what's there and then hard code it :)

I'd be more concerned if it was an ongoing requirement.

Anyway, continuing to test the query, will get back to you both shortly.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40219790
Given the 'wonderful' design, I can feel your pain...  ;)

If it is an ETL type question it might be easier with PL/SQL.  If that is an option, I'll try to get some free time later to experiment.
0
 
LVL 22

Author Closing Comment

by:Steve Wales
ID: 40219852
Actually, I have what appears to be working output.  Chaau's query, ever so slightly modified got me what I needed (I simplified the total query for the question, there were some extra tables that needed to be fitted in as well).

I will however file away slightwv's response for further analysis because there are all sorts of extra pieces there I've never played with before that need further review on my part.

Thank you both for the time and effort, it is greatly appreciated.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40219889
Depending on how much data you are dealing with I strongly encourage you to look at the execution plans between the two before deciding on which one to use.

With your simple test data the one I posted is a LOT better in terms of table access.
0
 
LVL 22

Author Comment

by:Steve Wales
ID: 40219909
Oh I'm sure that the execution plan is ugly as all get out ...  I got about 40,000 rows of output and as I said, it's a one off query that completed in about 60 seconds - so for one and done I can live with it.

If I was something that was running all the time, I'd take the time to analyze and fully understand the query you dropped in there, but for now, there's more data dumps they need and I'm afraid I'll have to store that away to come back to a little later - because having a nice and tidy way to merge two one to many relationships into a single output is definitely a tool I want in my toolbox for future use.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now