LeeHopkins
asked on
How to do a full outer join with two queries
I have a problem I have two queries, I need to do a full outer join between the two querys
where the PN = PN i created a view of each query and it did the join and it works great, but my problem is that the parameters are changed for each query. so I cant use views in my appliation.
here is my view that works
SELECT V1.bussunit, V1.family, V1.pn, V1.descrip, V1.com_code,
Case when v2.stdcost is Null then 0 else cast(round(V2.stdcost,3) as numeric(10,3)) end AS PrevVal,
Case when v1.stdcost is null then 0 else cast(round(V1.stdcost,3) as numeric(10,3)) end AS currval, V1.OHqty
FROM V1 full outer join
V2 ON V1.pn = V2.pn
here are my two queries
(V1)
Select bussunit, family, pn, descrip, com_code,
'' as Prevstd, stdcost, '' as variance, OHqty, '' OHReval
from onhand_stdcosthist
where family = 'ABB'
and lastdownloaded = '04/02/2007'
order by 1
(v2)
Select bussunit, family, pn, descrip, com_code,
'' as Prevstd, stdcost, '' as variance, OHqty, '' OHReval
from onhand_stdcosthist
where family = 'ABB'
and lastdownloaded = '05/29/2007'
order by 1
where the PN = PN i created a view of each query and it did the join and it works great, but my problem is that the parameters are changed for each query. so I cant use views in my appliation.
here is my view that works
SELECT V1.bussunit, V1.family, V1.pn, V1.descrip, V1.com_code,
Case when v2.stdcost is Null then 0 else cast(round(V2.stdcost,3) as numeric(10,3)) end AS PrevVal,
Case when v1.stdcost is null then 0 else cast(round(V1.stdcost,3) as numeric(10,3)) end AS currval, V1.OHqty
FROM V1 full outer join
V2 ON V1.pn = V2.pn
here are my two queries
(V1)
Select bussunit, family, pn, descrip, com_code,
'' as Prevstd, stdcost, '' as variance, OHqty, '' OHReval
from onhand_stdcosthist
where family = 'ABB'
and lastdownloaded = '04/02/2007'
order by 1
(v2)
Select bussunit, family, pn, descrip, com_code,
'' as Prevstd, stdcost, '' as variance, OHqty, '' OHReval
from onhand_stdcosthist
where family = 'ABB'
and lastdownloaded = '05/29/2007'
order by 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I ran the following query as test, I do no know why you are getting the problem
Select a.col1, b.col1, a.col2, b.col2 from
(Select * from (select 1 as col1, 'a' as col2
Union
Select 2, 'b'
Union
Select 3, 'c')x) a FULL OUTER JOIN
(Select * from (select 2 as col1, 'a' as col2
Union
Select 3, 'b'
Union
Select 4, 'c')y) b on a.col1 = b.col1
Select a.col1, b.col1, a.col2, b.col2 from
(Select * from (select 1 as col1, 'a' as col2
Union
Select 2, 'b'
Union
Select 3, 'c')x) a FULL OUTER JOIN
(Select * from (select 2 as col1, 'a' as col2
Union
Select 3, 'b'
Union
Select 4, 'c')y) b on a.col1 = b.col1
ASKER
I had a cramp I did something stupid
select ..... from
(.....) a,
(.....)b
where a.pn = b.pn
select ..... from
(.....) a,
(.....)b
where a.pn = b.pn
ASKER
unlike the full outter join will leave a null value for me in the missing field