UNION Query drops records

Question

Post reply

UNION Query drops records

joco

Grasshopper

Points: 11
More actions
September 16, 2008 at 11:55 am

#190845

OK this is weird... I have a simple two part UNION query. When I run each query separately I get 4 records and zero records for the second query... as it should.
BUT when I run the queries together (as a UNION) I would expect 4 records... but I only get three, Whats the deal??
Select
F_CustomerLedger.BusUnitID,
F_CustomerLedger.TenantID,
GLOffsetCd,
OpenAmt,
InvoiceAmt,
InvoiceDt,
null as CurrentAmt,
null as Day30Amt,
null as Day60Amt,
null as Day90Amt,
null as Day120Amt,
null as Day180Amt,
null as Plus180Amt,
null as Cust1Amt,
null as CalDate,
D_Tenant.TenantDesc
From
F_CustomerLedger
INNER JOIN [dbo].[D_Tenant] [D_Tenant]
ON [F_CustomerLedger].[TenantID] = [D_Tenant].[TenantID]
INNER JOIN
D_BusinessUnit ON F_CustomerLedger.BusUnitID = D_BusinessUnit.BusUnitID
Where
F_CustomerLedger.OpenAmt <> '0.00'
And
F_CustomerLedger.TenantID='1005891' and
D_BusinessUnit.BusUnitSC='5601'
Returns
BusUnitID TenantID GLOffsetCd OpenAmt InvoiceAmt InvoiceDt CurrentAmt Day30Amt Day60Amt Day90Amt Day120Amt Day180Amt Plus180Amt Cust1Amt CalDate TenantDesc
------------ ----------- ------------- ---------- ------------- --------------------- ------------- ----------- ----------- ----------- ------------ ------------ ------------- ----------- ---------- -------------------
40863 1005891 UC -500 -500 8/1/2008 12:00:00 AM (null) (null) (null) (null) (null) (null) (null) (null) (null) Grace & Arthur Shyi
40863 1005891 UC -200 -700 8/26/2008 12:00:00 AM (null) (null) (null) (null) (null) (null) (null) (null) (null) Grace & Arthur Shyi
40863 1005891 UC -200 -700 8/26/2008 12:00:00 AM (null) (null) (null) (null) (null) (null) (null) (null) (null) Grace & Arthur Shyi
40863 1005891 REI 178.61 178.61 6/27/2008 12:00:00 AM (null) (null) (null) (null) (null) (null) (null) (null) (null) Grace & Arthur Shyi
But when I run as part of a UNION I only get three records
UNION Query
Select
F_CustomerLedger.BusUnitID,
F_CustomerLedger.TenantID,
GLOffsetCd,
OpenAmt,
InvoiceAmt,
InvoiceDt,
null as CurrentAmt,
null as Day30Amt,
null as Day60Amt,
null as Day90Amt,
null as Day120Amt,
null as Day180Amt,
null as Plus180Amt,
null as Cust1Amt,
null as CalDate,
D_Tenant.TenantDesc
From
F_CustomerLedger
INNER JOIN [dbo].[D_Tenant] [D_Tenant]
ON [F_CustomerLedger].[TenantID] = [D_Tenant].[TenantID]
INNER JOIN
D_BusinessUnit ON F_CustomerLedger.BusUnitID = D_BusinessUnit.BusUnitID
Where
F_CustomerLedger.OpenAmt <> '0.00'
And
F_CustomerLedger.TenantID='1005891' and
D_BusinessUnit.BusUnitSC='5601'
UNION
Select
F_CustomerAging.BusUnitID,
F_CustomerAging.TenantID,
GLOffsetCd,
null as OpenAmt,
null as InvoiceAmt,
null as InvoiceDt,
CurrentAmt,
Day30Amt,
Day60Amt,
Day90Amt,
Day120Amt,
Day180Amt,
Plus180Amt,
Cust1Amt,
[D_FiscalDate].[CalDate],
D_Tenant.TenantDesc
from F_CustomerAging
INNER JOIN [dbo].[D_FiscalDate] [D_FiscalDate]
ON [F_CustomerAging].[DateID] = [D_FiscalDate].[DateID]
INNER JOIN [dbo].[D_Tenant] [D_Tenant]
ON [F_CustomerAging].[TenantID] = [D_Tenant].[TenantID]
INNER JOIN
D_BusinessUnit ON F_CustomerAging.BusUnitID = D_BusinessUnit.BusUnitID
Where
F_CustomerAging.TenantID='1005891' and
D_BusinessUnit.BusUnitSC='5601'
and ([D_FiscalDate].[PatternCd] ='3')
and D_FiscalDate.CalDate='09/14/08'
Order By TenantID
I get this:
BusUnitID TenantID GLOffsetCd OpenAmt InvoiceAmt InvoiceDt CurrentAmt Day30Amt Day60Amt Day90Amt Day120Amt Day180Amt Plus180Amt Cust1Amt CalDate TenantDesc
------------ ----------- ------------- ---------- ------------- --------------------- ------------- ----------- ----------- ----------- ------------ ------------ ------------- ----------- ---------- -------------------
40863 1005891 REI 178.61 178.61 6/27/2008 12:00:00 AM (null) (null) (null) (null) (null) (null) (null) (null) (null) Grace & Arthur Shyi
40863 1005891 UC -500 -500 8/1/2008 12:00:00 AM (null) (null) (null) (null) (null) (null) (null) (null) (null) Grace & Arthur Shyi
40863 1005891 UC -200 -700 8/26/2008 12:00:00 AM (null) (null) (null) (null) (null) (null) (null) (null) (null) Grace & Arthur Shyi
Do UNION's do somthing I am not aware of???

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

Try using UNION ALL as just UNION will combine results and remove duplicates.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot