make 1 record with Union statement

Hi there,
I've made a join query:
SELECT B.ITEMDESC AS ITEMDESC, A.ITEMNMBR AS ITEMNMBR, 0 AS 'SUM QTYORDER',
A.QTYONHND AS 'QTYONHND'
FROM IV00102 A LEFT JOIN
IV00101 B ON A.ITEMNMBR = B.ITEMNMBR
WHERE A.RCRDTYPE IN (2) AND A.LOCNCODE IN ('SALES') AND A.ITEMNMBR =
'S.NL.543'
GROUP BY B.ITEMDESC, A.ITEMNMBR, A.LOCNCODE, A.QTYONHND, A.QTYBKORD,
A.ATYALLOC, A.QTYSOLD
UNION
SELECT ITEMDESC AS ITEMDESC, ITEMNMBR AS ITEMNMBR, SUM(QTYORDER) AS 'SUM
QTYORDER',
0 AS 'QTYONHND'
FROM POP10110
WHERE POLNESTA IN (2) AND ITEMNMBR = 'S.NL.543'
GROUP BY ITEMDESC, ITEMNMBR
the result looks like this:
ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
Proactiv (ITEM) S.NL.543
0 -477
Proactiv (ITEM) S.NL.543
6000 0
what I want is to produce 1 record which will look like this:
ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
Proactiv (ITEM) S.NL.543
6000 -477
any suggestions on how to do this?
Thanks in advance,
SusannaUse the UNION-ed query as a derived table & use aggregate functions on the
outer query. i.e :
SELECT MAX( itemdesc ) AS "item_desc",
MAX( itemnbr ) AS "item_nbr",
...
FROM ( < your query with UNION > ) D
Anith|||What are you doing here? It could either be the max, or adding. I am
assuming that it doesn't matter because the 0 values actually mean not
applicable in your main query.

> ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
> Proactiv (ITEM) S.NL.543
> 0 -477
> Proactiv (ITEM) S.NL.543
> 6000 0
Like anith says, you can just do a group, or possibly something like this,
assuming that you are actually putting out one row per ITEMMBR (if not the
union isn't going to work well either)
SELECT B.ITEMDESC AS ITEMDESC, A.ITEMNMBR AS ITEMNMBR, A.QTYONHND AS
'QTYONHND',
(SELECT SUM(QTYORDER) AS 'SUM QTYORDER'
FROM POP10110
WHERE POLNESTA IN (2) AND ITEMNMBR =
A.ITEMNMBR) as 'QTYORDER'
FROM IV00102 A
LEFT JOIN IV00101 B
ON A.ITEMNMBR = B.ITEMNMBR
WHERE A.RCRDTYPE IN (2)
AND A.LOCNCODE IN ('SALES')
AND A.ITEMNMBR = 'S.NL.543'
GROUP BY B.ITEMDESC, A.ITEMNMBR, A.LOCNCODE, A.QTYONHND, A.QTYBKORD,
A.ATYALLOC, A.QTYSOLD
Do you have some issues with poor data quality? I notice that ITEMDESC
comes from an outer join (which makes me wonder what that table is for) and
what the uniqueness is on. In your second query it seems to be itemnmber.
If it is, this should work (or something close.)
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Susanna" <Susanna@.discussions.microsoft.com> wrote in message
news:5D04D744-C679-487D-85F9-8AFBC2D3A844@.microsoft.com...
> Hi there,
> I've made a join query:
> SELECT B.ITEMDESC AS ITEMDESC, A.ITEMNMBR AS ITEMNMBR, 0 AS 'SUM
> QTYORDER',
> A.QTYONHND AS 'QTYONHND'
> FROM IV00102 A LEFT JOIN
> IV00101 B ON A.ITEMNMBR = B.ITEMNMBR
> WHERE A.RCRDTYPE IN (2) AND A.LOCNCODE IN ('SALES') AND A.ITEMNMBR =
> 'S.NL.543'
> GROUP BY B.ITEMDESC, A.ITEMNMBR, A.LOCNCODE, A.QTYONHND, A.QTYBKORD,
> A.ATYALLOC, A.QTYSOLD
> UNION
> SELECT ITEMDESC AS ITEMDESC, ITEMNMBR AS ITEMNMBR, SUM(QTYORDER) AS 'SUM
> QTYORDER',
> 0 AS 'QTYONHND'
> FROM POP10110
> WHERE POLNESTA IN (2) AND ITEMNMBR = 'S.NL.543'
> GROUP BY ITEMDESC, ITEMNMBR
> the result looks like this:
> ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
> Proactiv (ITEM) S.NL.543
> 0 -477
> Proactiv (ITEM) S.NL.543
> 6000 0
> what I want is to produce 1 record which will look like this:
> ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
> Proactiv (ITEM) S.NL.543
> 6000 -477
> any suggestions on how to do this?
> --
> Thanks in advance,
> Susanna