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


LeeHopkinsAsked:
Who is Participating?
 
srafi78Connect With a Mentor Commented:
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
(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')V1 FULL OUTER JOIN
(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')V2
ON
V1.pn = V2.pn
ORDER BY 1
0
 
LeeHopkinsAuthor Commented:
I am trying this however when there is an instance of v1 or v2 does not have a match then i am missing  some data.
unlike the full outter join will leave a null value for me in the missing field
0
 
srafi78Commented:
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
0
 
LeeHopkinsAuthor Commented:
I had a cramp I did something stupid
select ..... from
(.....) a,
(.....)b
where a.pn = b.pn
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.