UNION ALL query - HELP!

[Edward]

I am having conceptual trouble with the following query:

select r.ServiceID,r.ContractID,sum(Total)
from (
select
csc.ServiceID,c.ContractID, sum(csc.ContainerMovement) as Total
from
iwms_tbl_CustomerSiteContainers csc,
iwms_tbl_ContractLines cl,
iwms_tbl_Contracts c,
iwms_tbl_ContractLinePricing clp
where
clp.ContractLineID = cl.ContractLineID and
clp.ContractPriceLineDescription = 'Rental' and
clp.ContractPriceLineActive=1 and
clp.ContractPriceLineExpiry > getdate() and
csc.ServiceID > 1 and
c.ContractID = cl.ContractID and
c.ContractStatusCode = 5 and
cl.ServiceID = csc.ServiceID
group by
c.ContractID,csc.ServiceID
union all
select
si.ServiceID,c.ContractID,sum(j.QuantityCollected)-sum(j.QuantityDelivered)
as Total
from
iwms_tbl_Jobs j,
iwms_tbl_ServiceInstances si,
iwms_tbl_ContractLines cl,
iwms_tbl_Contracts c,
iwms_tbl_ContractLinePricing clp
where
clp.ContractLineID = cl.ContractLineID and
clp.ContractPriceLineDescription = 'Rental' and
clp.ContractPriceLineActive=1 and
clp.ContractPriceLineExpiry > getdate() and
c.ContractID = cl.ContractID and
c.ContractStatusCode = 5 and
cl.ServiceID = si.ServiceID and
j.JobStatusCode <> 80 and
j.ServiceInstanceID = si.ServiceInstanceID 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********@hotmail.com (Edward) wrote in message news:<25**************************@posting.google. com>...

I am having conceptual trouble with the following query:

select r.ServiceID,r.ContractID,sum(Total)
from (
select
csc.ServiceID,c.ContractID, sum(csc.ContainerMovement) as Total
from
iwms_tbl_CustomerSiteContainers csc,
iwms_tbl_ContractLines cl,
iwms_tbl_Contracts c,
iwms_tbl_ContractLinePricing clp
where
clp.ContractLineID = cl.ContractLineID and
clp.ContractPriceLineDescription = 'Rental' and
clp.ContractPriceLineActive=1 and
clp.ContractPriceLineExpiry > getdate() and
csc.ServiceID > 1 and
c.ContractID = cl.ContractID and
c.ContractStatusCode = 5 and
cl.ServiceID = csc.ServiceID
group by
c.ContractID,csc.ServiceID
union all
select
si.ServiceID,c.ContractID,sum(j.QuantityCollected)-sum(j.QuantityDelivered)
as Total
from
iwms_tbl_Jobs j,
iwms_tbl_ServiceInstances si,
iwms_tbl_ContractLines cl,
iwms_tbl_Contracts c,
iwms_tbl_ContractLinePricing clp
where
clp.ContractLineID = cl.ContractLineID and
clp.ContractPriceLineDescription = 'Rental' and
clp.ContractPriceLineActive=1 and
clp.ContractPriceLineExpiry > getdate() and
c.ContractID = cl.ContractID and
c.ContractStatusCode = 5 and
cl.ServiceID = si.ServiceID and
j.JobStatusCode <> 80 and
j.ServiceInstanceID = si.ServiceInstanceID 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.ContractID --,sum(Total)
.....
/***group by r.ContractID,r.ServiceID
having sum(Total) <> 0 ***/

[Erland Sommarskog]

Edward (te********@hotmail.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