UNION ALL query - HELP!

[Edward]

I am having conceptual trouble with the following query:

select r.ServiceID,r.C ontractID,sum(T otal)
from (
select
csc.ServiceID,c .ContractID, sum(csc.Contain erMovement) as Total
from
iwms_tbl_Custom erSiteContainer s csc,
iwms_tbl_Contra ctLines cl,
iwms_tbl_Contra cts c,
iwms_tbl_Contra ctLinePricing clp
where
clp.ContractLin eID = cl.ContractLine ID and
clp.ContractPri ceLineDescripti on = 'Rental' and
clp.ContractPri ceLineActive=1 and
clp.ContractPri ceLineExpiry > getdate() and
csc.ServiceID > 1 and
c.ContractID = cl.ContractID and
c.ContractStatu sCode = 5 and
cl.ServiceID = csc.ServiceID
group by
c.ContractID,cs c.ServiceID
union all
select
si.ServiceID,c. ContractID,sum( j.QuantityColle cted)-sum(j.QuantityD elivered)
as Total
from
iwms_tbl_Jobs j,
iwms_tbl_Servic eInstances si,
iwms_tbl_Contra ctLines cl,
iwms_tbl_Contra cts c,
iwms_tbl_Contra ctLinePricing clp
where
clp.ContractLin eID = cl.ContractLine ID and
clp.ContractPri ceLineDescripti on = 'Rental' and
clp.ContractPri ceLineActive=1 and
clp.ContractPri ceLineExpiry > getdate() and
c.ContractID = cl.ContractID and
c.ContractStatu sCode = 5 and
cl.ServiceID = si.ServiceID and
j.JobStatusCode <> 80 and
j.ServiceInstan ceID = si.ServiceInsta nceID and
si.ServiceID > 1
group by
c.ContractID,si .ServiceID
) as r
group by
r.ContractID,r. ServiceID
having
sum(Total) <> 0
order by r.ContractID

It returns 140 rows. However, if I comment out the first select
statement inside the brackets (select csc.ServiceID,c .ContractID
....union all) and run it, it returns 4,785 rows. If I comment out the
second select statement (union all ...group by
c.ContractID,si .ServiceID) it returns 4,786 rows. So why doesn't the
*whole* thing return 9,571 rows? That's what I thought a UNION did -
append the results of one select to the bottom of the second select.

I will supply table defs if it will help, but there's a lot of stuff
here and I think it isn't a data question, but an
I-don't-understand-the-SQL question!

TIA

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

[xAvailx]

Edward:

My guess is that you have duplicates in there. Try using Union All.

HTH,

Thanks,

Berny Zamora

te********@hotm ail.com (Edward) wrote in message news:<25******* *************** ****@posting.go ogle.com>...

I am having conceptual trouble with the following query:

select r.ServiceID,r.C ontractID,sum(T otal)
from (
select
csc.ServiceID,c .ContractID, sum(csc.Contain erMovement) as Total
from
iwms_tbl_Custom erSiteContainer s csc,
iwms_tbl_Contra ctLines cl,
iwms_tbl_Contra cts c,
iwms_tbl_Contra ctLinePricing clp
where
clp.ContractLin eID = cl.ContractLine ID and
clp.ContractPri ceLineDescripti on = 'Rental' and
clp.ContractPri ceLineActive=1 and
clp.ContractPri ceLineExpiry > getdate() and
csc.ServiceID > 1 and
c.ContractID = cl.ContractID and
c.ContractStatu sCode = 5 and
cl.ServiceID = csc.ServiceID
group by
c.ContractID,cs c.ServiceID
union all
select
si.ServiceID,c. ContractID,sum( j.QuantityColle cted)-sum(j.QuantityD elivered)
as Total
from
iwms_tbl_Jobs j,
iwms_tbl_Servic eInstances si,
iwms_tbl_Contra ctLines cl,
iwms_tbl_Contra cts c,
iwms_tbl_Contra ctLinePricing clp
where
clp.ContractLin eID = cl.ContractLine ID and
clp.ContractPri ceLineDescripti on = 'Rental' and
clp.ContractPri ceLineActive=1 and
clp.ContractPri ceLineExpiry > getdate() and
c.ContractID = cl.ContractID and
c.ContractStatu sCode = 5 and
cl.ServiceID = si.ServiceID and
j.JobStatusCode <> 80 and
j.ServiceInstan ceID = si.ServiceInsta nceID and
si.ServiceID > 1
group by
c.ContractID,si .ServiceID
) as r
group by
r.ContractID,r. ServiceID
having
sum(Total) <> 0
order by r.ContractID

It returns 140 rows. However, if I comment out the first select
statement inside the brackets (select csc.ServiceID,c .ContractID
...union all) and run it, it returns 4,785 rows. If I comment out the
second select statement (union all ...group by
c.ContractID,si .ServiceID) it returns 4,786 rows. So why doesn't the
*whole* thing return 9,571 rows? That's what I thought a UNION did -
append the results of one select to the bottom of the second select.

I will supply table defs if it will help, but there's a lot of stuff
here and I think it isn't a data question, but an
I-don't-understand-the-SQL question!

TIA

Edward

[louis nguyen]

> It returns 140 rows. However, if I comment out the first select

statement inside the brackets (select csc.ServiceID,c .ContractID
...union all) and run it, it returns 4,785 rows. If I comment out the
second select statement (union all ...group by
c.ContractID,si .ServiceID) it returns 4,786 rows. So why doesn't the
*whole* thing return 9,571 rows? That's what I thought a UNION did -
append the results of one select to the bottom of the second select.

Hi,

I think if you comment out the outer-most group by and having clauses
and the "sum(total) ", it will return 9,571 rows.

select r.ServiceID,r.C ontractID --,sum(Total)
.....
/***group by r.ContractID,r. ServiceID
having sum(Total) <> 0 ***/

[Erland Sommarskog]

Edward (te********@hot mail.com) writes:

It returns 140 rows. However, if I comment out the first select
statement inside the brackets (select csc.ServiceID,c .ContractID
...union all) and run it, it returns 4,785 rows. If I comment out the
second select statement (union all ...group by
c.ContractID,si .ServiceID) it returns 4,786 rows. So why doesn't the
*whole* thing return 9,571 rows? That's what I thought a UNION did -
append the results of one select to the bottom of the second select.

Yeah, then you have this at the end:

group by r.ContractID,r. ServiceID
having sum(Total) <> 0

So obviously most combinations of ContraceID and ServiceID in the two
virtual table has a matching Total, but not all.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp