| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
03-23-10, 13:16
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 5
|
|
|
nested join instead of union?
|
|
I've been googling for a week and have learned a lot about sql but cannot solve my problem
basically here's what i want:
all rows in t2wrwrh with master_wo_nbr = 1551785
and
all rows in t2wowoh with master_wo_nbr = 1551785
and
all rows in t2msmsh with wo_nbr = to wo_nbr in t2wowoh
the same wo_nbr can be in all three tables
i've tried a full outer join on the first two tables, but it takes about an hour to execute.
the query i have so far (below) only takes a minute to execute but i have duplicate rows resulting where placeholders for the union are used and the wo_nbr exists in all three tables. I couldn't get a nested join to work, which seems like what i need...
Code:
select coalesce(a.wo_nbr, '0000000') as wo_nbr,
coalesce(a.wkr_nbr, '0000000') as wkr_nbr,
coalesce(a.wo_desc, ' ') as wo_desc,
coalesce(a.perf_dept, ' ') as wo_perf_dept,
coalesce(a.wo_status, ' ') as wo_status,
coalesce(a.wo_stat_date, '0001-01-01') as wo_stat_date,
coalesce(a.est_tot_cost, 0) as est_tot_cost,
coalesce(a.tot_cost, 0) as tot_cost,
coalesce(a.est_labour, 0) as est_labour,
coalesce(a.labour_cost, 0) as labour_cost,
coalesce(a.est_dir_matl, 0) as est_dir_matl,
coalesce(a.dir_matl_cost, 0) as dir_matl_cost,
coalesce(a.est_stores, 0) as est_stores,
coalesce(a.stores_cost, 0) as stores_cost,
coalesce(a.est_other_chg, 0) as est_other_chg,
coalesce(a.other_chg, 0) as other_chg,
coalesce(a.est_manhrs, 0) as est_manhrs,
coalesce(a.manhrs, 0) as manhrs,
coalesce(b.wkr_nbr, '0000000') as wkr_nbr,
coalesce(b.wo_nbr, '0000000')as wo_nbr,
coalesce(b.wkr_desc, ' ') as wkr_desc,
coalesce(b.rqst_dept, ' ') as rqst_dept,
coalesce(b.perf_dept, ' ') as perf_dept,
coalesce(b.wkr_status, ' ') as wkr_status,
coalesce(b.wkr_stat_date, '0001-01-01') as wkr_stat_date,
coalesce(b.est_plan_hrs, 0)as est_plan_hrs,
'0000000' as msr_nbr,
' ' as msr_status,
' ' as msr_purpose,
0 as msr_est_value,
'0000000' as wkr_nbr,
'0000000' as wo_nbr,
'00' as wo_task
from ats.t2wrwrh b left outer join ats.t2wowoh a
on b.wo_nbr = a.wo_nbr
where b.master_wo_nbr = '1551785'
union
select a.wo_nbr,
a.wkr_nbr,
a.wo_desc,
a.perf_dept,
a.wo_status,
a.wo_stat_date,
a.est_tot_cost,
a.tot_cost,
a.est_labour,
a.labour_cost,
a.est_dir_matl,
a.dir_matl_cost,
a.est_stores,
a.stores_cost,
a.est_other_chg,
a.other_chg,
a.est_manhrs,
a.manhrs,
'0000000',
'0000000',
' ',
' ',
' ',
' ',
'0001-01-01',
0,
coalesce(c.msr_nbr, '0000000'),
coalesce(c.msr_status, ' '),
coalesce(c.msr_purpose, ' '),
coalesce(c.msr_est_value, 0),
coalesce(c.wkr_nbr, '0000000'),
coalesce(c.wo_nbr, '0000000'),
coalesce(c.wo_task, '00')
from ats.t2wowoh a left outer join ats.t2msmsh c
on a.wo_nbr = c.wo_nbr
where a.master_wo_nbr = '1551785'
order by 1,2,3
perhaps there's a simple concept i'm missing as a self taught programmer...
if you could help it would be very appreciated...
|
|
03-23-10, 13:28
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
What DB2 version and OS are you using? Can you give an example of what you have in the tables and what you expect to get as a result of the query?
Andy
|
|
03-23-10, 14:01
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 5
|
|
|
|
I don't know the version of DB2, I believe it's running on Server 2008.
here is what i'm getting (with the unimportant columns removed)
Code:
WO_NBR WKR_NBR WKR_NBR WO_NBR MSR_NBR
1532493 0000000 0000000 0000000 0000000
1532555 0000000 0000000 0000000 0000000
1539839 0291539 0000000 0000000 02653120
1539839 0291539 0291539 1539839 0000000
1546929 0000000 0000000 0000000 02568270
1549916 0292982 0000000 0000000 0000000
1549916 0292982 0292982 1549916 0000000
1549939 0292983 0000000 0000000 0000000
1549939 0292983 0292983 1549939 0000000
1549942 0292984 0000000 0000000 0000000
1549942 0292984 0292984 1549942 0000000
1549945 0292989 0000000 0000000 0000000
1549945 0292989 0292989 1549945 0000000
1549946 0292990 0000000 0000000 0000000
1549946 0292990 0292990 1549946 0000000
1549948 0292991 0000000 0000000 0000000
1549948 0292991 0292991 1549948 0000000
1549951 0292992 0000000 0000000 0000000
1549951 0292992 0292992 1549951 0000000
1551768 0000000 0000000 0000000 0000000
1559486 0000000 0000000 0000000 0000000
Here's what I would like
Code:
WO_NBR WKR_NBR WKR_NBR WO_NBR MSR_NBR
1532493 0000000 0000000 0000000 0000000
1532555 0000000 0000000 0000000 0000000
1539839 0291539 0291539 1539839 02653120
1546929 0000000 0000000 0000000 02568270
1549916 0292982 0292982 1549916 0000000
1549939 0292983 0292983 1549939 0000000
1549942 0292984 0292984 1549942 0000000
1549945 0292989 0292989 1549945 0000000
1549946 0292990 0292990 1549946 0000000
1549948 0292991 0292991 1549948 0000000
1549951 0292992 0292992 1549951 0000000
1551768 0000000 0000000 0000000 0000000
1559486 0000000 0000000 0000000 0000000
the row where wo_nbr is 1539839 is the best example of the duplicate I referred to.
|
|
03-23-10, 14:05
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
OK, I see where you want to get to, but where are you coming from? How about a sample of the data in the tables you are querying?
Andy
|
|
03-23-10, 15:14
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 5
|
|
Well at a basic level
t2wrwrh
keyed on wkr_nbr
may or may not have wo_nbr value
may or may not have master_wo_nbr value
does not have msr_nbr
Code:
WKR_NO WO_NBR MASTER _WO NBR
0291539 1539839 1551785
0291656 1651369 1551785
0291657 1609503 1551785
0292045 0000000 1551785
0292047 1579197 1551785
0292048 0000000 1551785
0292050 1579203 1551785
0292057 1579200 1551785
0292077 1583240 1551785
0292763 1575796 1551785
0292764 1575794 1551785
0292982 1549916 1551785
0292983 1549939 1551785
0292984 1549942 1551785
0292989 1549945 1551785
0292990 1549946 1551785
0292991 1549948 1551785
0292992 1549951 1551785
0293018 1568471 1551785
0293120 1575137 1551785
0293248 0000000 1551785
t2wowoh
keyed on wo_nbr
may or may not have wkr_nbr value
may or may not have master_wo_nbr
may or may not have msr_nbr value
Code:
WO_NBR WKR_NBR MASTER_WO_NBR
1532493 0000000 1551785
1532555 0000000 1551785
1539839 0291539 1551785
1546929 0000000 1551785
1549916 0292982 1551785
1549939 0292983 1551785
1549942 0292984 1551785
1549945 0292989 1551785
1549946 0292990 1551785
1549948 0292991 1551785
1549951 0292992 1551785
1551768 0000000 1551785
1559486 0000000 1551785
1565582 0000000 1551785
1568471 0293018 1551785
1571148 0295701 1551785
1575135 0295907 1551785
1575137 0293120 1551785
1575785 0291498 1551785
1575789 0291095 1551785
1575793 0291160 1551785
t2msmh
keyed on msr_nbr
may or may not have wo_nbr value
may or may not have wkr_nbr value
does not have master_wo_nbr
Code:
MSR_NBR WO_NBR WKR_NBR
00384931 0038947 0000000
00384941 0035857
00384986 0038991 0000000
00385000 0036436
00385012 0038996
00385013 0038997
00385030 0035786
00385124 0037631
00385127 0039010
00385156 0039013
00385157 0039014
00385169 0039017
00385184 0039018
00385188 0039019
00385191 0039020
00385196 0038998
00385213 0038637
00385218 0038642
00385222 0038639
00385227 0038640
|
|
03-23-10, 15:35
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
I think I see what you want. Do not use a UNION, that is giving you the duplicates. For your result set, where is each column coming from?
Andy
|
|
03-23-10, 15:43
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 5
|
|
Code:
+---t2wowoh----+----t2wrwrh----+-t2msmsh-+
WO_NBR WKR_NBR WKR_NBR WO_NBR MSR_NBR
1532493 0000000 0000000 0000000 0000000
1532555 0000000 0000000 0000000 0000000
1539839 0291539 0291539 1539839 02653120
1546929 0000000 0000000 0000000 02568270
1549916 0292982 0292982 1549916 0000000
1549939 0292983 0292983 1549939 0000000
1549942 0292984 0292984 1549942 0000000
1549945 0292989 0292989 1549945 0000000
1549946 0292990 0292990 1549946 0000000
1549948 0292991 0292991 1549948 0000000
1549951 0292992 0292992 1549951 0000000
1551768 0000000 0000000 0000000 0000000
1559486 0000000 0000000 0000000 0000000
I knew the UNION was giving the duplicates, but wasn't sure if there was something that would let me merge the rows during the union...
|
Last edited by joseph.miskiw; 03-23-10 at 15:46.
Reason: added comments
|
03-23-10, 15:53
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
I believe this is what you want:
Code:
select o.wo_nbr,o.wkr_nbr,r.wknbr,r.wo_nbr,s.msr_nbr
from t2wrwrh as r
left outer join t2wowoh as o on ()
left outer join t2msmsh as s on ()
where r.master_wo_nbr = 1551785
Andy
|
|
03-23-10, 16:20
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 5
|
|
well that's nice and tidy... i had to make a few changes in order for it to run, i guess it wasn't clear in my samples, but your query doesn't pull rows where there is a wo_nbr but no wkr_nbr in the t2wowoh table, i think the following is what i really want
Code:
select o.wo_nbr,o.wkr_nbr,r.wknbr,r.wo_nbr,s.msr_nbr
from t2wrwrh as r
full outer join t2wowoh as o on ()
left outer join t2msmsh as s on ()
where r.master_wo_nbr = 1551785 or o.master_wo_nbr = 1551785
this takes a bit longer to run but i think it works...
thanks for the help, as a self-taught programmer i'm often down a rabbit hole... it's easy to miss fundamental concepts when you teach yourself...
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
nested join instead of union?
