Union Combine

[robert030975]

HI I have a problem that I can't seem to do in one select.


fullcode gbbin qbbin pqty gbqty

N3A2020/N050/L 0 270A1 0 11
N3A2020/N050/L 253A1 0 22 0


Now the productcode and bins are varchar but what I want to see without have to create two tables and join
is have the select into one row

N3A2020/N050/L 253A1 270A1 22 11

Now I can sum both the qty's but how do I get round the bin.


FYI heres the openquery union select

Select Fullcode, gbbin, qbbin , pqty, gbqty from (
Select fullcode, '0' as gbbin, bin as qbbin, sum ( physicalqty) as pqty , 0 as gbqty from openquery ( CERP, '
SELECT
st_stbin.whse,
st_stbin.bin,
st_stbin.physicalqty,
st_stbin.allocateqty,
st_stbin.physicalqty - st_stbin.allocateqty,
st_stock.fullcode,
st_stock.descr
FROM
st_stbin,
st_stock,
jv_missstock
WHERE
( jv_missstock.stockhash=st_stock.hash )
AND ( jv_missstock.stockhash=st_stbin.stockhash )
AND
(
st_stbin.whse = ''QB01''
AND
st_stbin.bin BETWEEN ''270A1'' AND ''290A1''
AND
st_stock.fullcode[1,3] BETWEEN ''N3A'' AND ''N3A''
AND
st_stbin.physicalqty > 0
)' )
Group by fullcode, bin
UNION
Select fullcode, bin as gbbin, '0' as qbbin, 0 as pqty , sum ( physicalqty) as gbqty from openquery ( CERP, '
SELECT
st_stbin.whse,
st_stbin.bin,
st_stbin.physicalqty,
st_stbin.allocateqty,
st_stbin.physicalqty - st_stbin.allocateqty,
st_stock.fullcode,
st_stock.descr
FROM
st_stbin,
st_stock,
jv_missstock
WHERE
( jv_missstock.stockhash=st_stock.hash )
AND ( jv_missstock.stockhash=st_stbin.stockhash )
AND
(
st_stbin.whse = ''GB01''
AND
st_stock.fullcode[1,3] BETWEEN ''N3A'' AND ''N3A''
AND
st_stbin.physicalqty > 0
)' )
Group by fullcode, bin )a



Any Help whould be great!!

 

[Crowley16]

not sure if I read your query correctly, so please excuse me if this is totally off the mark, but it seems to me that you'd be better not to bother with the union query at all, and just directly join the 2 seperate base queries, and leave off the padded columns.

If you're using 2005, then setup the 2 queries as 2 cte's, otherwise, use 2 derived tables. something like:

;with a as
(
select code, col1, col2 from openquery...
)
, b as
(
select code, col3, col4 from openquery...
)
select a.code, a.col1, a.col2, b.col1, b.col2
from a inner join b on a.code = b.code

--------------------
Procrastinate Now!