CODEWITHSUNDEEP
sqlsql-server
mHelpMe
mHelpMe

Reputation: 6668

using coalesce doubles the number of records returns

I have two queries below. The 1st query returns 83 rows and is what I expect. Query 2 though returns 166 rows (so double the 1st query). The only difference is shown below in the select statement in both of pf and pc,

Query 1 Sedol

Query 2 Coalesce(ISIN, Sedol, BbergTicker) Id

I do not understand why this doubles the number of rows returned?

Query 1

;with pf as
(
    select Name, Sedol, Nominal, FundCode, FileCode 
    from tbl1L where FundCode = 'BUNM' and CashItem = 0 and FileCode in ('MAIN', 'REISTD')
), pc as
(
    select Name, Sedol, Nominal, FundCode, FileCode 
from tbl1C where FundCode = 'BUNM' and CashItem = 0 and FileCode in ('MAIN', 'REDIST')
)
select coalesce(pf.FundCode, pc.FundCode) Fund, coalesce(pf.FileCode, pc.FileCode) FileCode, 
coalesce(pf.Name, pc.Name) Name, coalesce(pf.Sedol, pc.Sedol) Sedol, 
isnull(pf.Nominal, 0) PfNom, isnull(pc.Nominal, 0) PcNom, 
isnull(isnull(pf.Nominal, 0) - isnull(pc.Nominal, 0), -999) NomDiff
from pf full outer join pc on pf.Sedol = pc.Sedol and pf.FileCode = pc.FileCode 
where isnull(isnull(pf.Nominal, 0) - isnull(pc.Nominal, 0),-999) <> 0

Query 2

;with pf as
(
    select Name, Coalesce(ISIN, Sedol, BbergTicker) Id, Nominal, FundCode, FileCode 
    from tbl1L where FundCode = 'BUNM' and CashItem = 0 and FileCode in ('MAIN', 'REISTD')
), pc as
(
    select Name, Coalesce(ISIN, Sedol, BbergTicker) Id, Nominal, FundCode, FileCode 
from tbl1C where FundCode = 'BUNM' and CashItem = 0 and FileCode in ('MAIN', 'REDIST')
)
select coalesce(pf.FundCode, pc.FundCode) Fund, coalesce(pf.FileCode, pc.FileCode) FileCode, 
coalesce(pf.Name, pc.Name) Name, coalesce(pf.Id, pc.Id) Id, coalesce(pf.Sedol, pc.Sedol) Sedol, 
isnull(pf.Nominal, 0) PfNom, isnull(pc.Nominal, 0) PcNom, 
isnull(isnull(pf.Nominal, 0) - isnull(pc.Nominal, 0), -999) NomDiff
from pf full outer join pc on pf.Id = pc.Id and pf.FileCode = pc.FileCode 
where isnull(isnull(pf.Nominal, 0) - isnull(pc.Nominal, 0),-999) <> 0

Upvotes: 0

Views: 85

Answers (1)

mohan111
mohan111

Reputation: 8865

Query 1 : 

pf.Sedol = pc.Sedol and pf.FileCode = pc.FileCode

Query 2 : 

pf.Id = pc.Id and pf.FileCode = pc.FileCode

In joins there is a difference in both queries .

Coalesce will not impact count in records

Upvotes: 3

Related Questions