Numbers are double

Question

Numbers are double

kennyhuang0108

Right there with Babe

Points: 733
More actions
January 11, 2017 at 4:32 pm

#325185

Hi All,
I have two SQLs. One brings the double number and the other one is not which has a correct number. I wonder why ? also please share if you have any idea to deal with this? thank you
version 1 --> duplicate/double number
select c.bf_fund_cd FUND, b.bf_orgn_cd ORG, sum(a.data)
from bf_data a, bf_orgn_cnsl_tbl b, bf_fund_cnsl_tbl c
where
a.bf_orgn_id= b.bf_orgn_id and
a.bf_fund_id = c.bf_fund_id and
a.bf_bdob_id= D.BF_BDOB_CHLD_ID and
a.bf_tm_perd_cd in (select distinct bf_tm_perd_chld_cd
from bf_tm_perd_cnsl_tbl
where bf_tm_perd_select_cd in ('FINALT'))
having sum(a.data) <> 0
group by c.bf_fund_cd,b.bf_orgn_cd
version 2 -- correct number
select c.bf_fund_cd FUND, b.bf_orgn_cd ORG, sum(a.data)
from
(select bf_orgn_id,bf_fund_id,bf_tm_perd_cd, data from bf_data group by bf_orgn_id,bf_fund_id,bf_tm_perd_cd,data )a
Left Join ( select bf_orgn_id, bf_orgn_cd from BF_ORGN_CNSL_TBL group by bf_orgn_id, bf_orgn_cd) b on b.bf_orgn_id=a.bf_orgn_id
left join ( select bf_fund_id, bf_fund_cd from BF_FUND_CNSL_TBL group by bf_fund_id, bf_fund_cd)c on c.bf_fund_id=a.bf_fund_id
where a.bf_tm_perd_cd in (select distinct bf_tm_perd_chld_cd
from bf_tm_perd_cnsl_tbl
where bf_tm_perd_select_cd in ('FINALT'))
having sum(a.data) <> 0
group by c.bf_fund_cd,b.bf_orgn_cd

Viewing 4 posts - 1 through 3 (of 3 total)

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

Looks like a problem with the data where the group by clauses in the sub-queries of the second query suppress the problem.

😎

Try running these three queries with and without the group by, most likely you will fin the problem when the number of rows are different.

select bf_orgn_id,bf_fund_id,bf_tm_perd_cd, data from bf_data group by bf_orgn_id,bf_fund_id,bf_tm_perd_cd,data

select bf_orgn_id, bf_orgn_cd from BF_ORGN_CNSL_TBL group by bf_orgn_id, bf_orgn_cd

select bf_fund_id, bf_fund_cd from BF_FUND_CNSL_TBL group by bf_fund_id, bf_fund_cd

kennyhuang0108 Right there with Babe Points: 733 More actions · Answer 2

Eirikur Eiriksson - Wednesday, January 11, 2017 10:25 PM
Looks like a problem with the data where the group by clauses in the sub-queries of the second query suppress the problem.:cool:Try running these three queries with and without the group by, most likely you will fin the problem when the number of rows are different.select bf_orgn_id,bf_fund_id,bf_tm_perd_cd, data from bf_data group by bf_orgn_id,bf_fund_id,bf_tm_perd_cd,data select bf_orgn_id, bf_orgn_cd from BF_ORGN_CNSL_TBL group by bf_orgn_id, bf_orgn_cd select bf_fund_id, bf_fund_cd from BF_FUND_CNSL_TBL group by bf_fund_id, bf_fund_cd

Thanks. I saw without group by the rows were doubled.
I wonder what the best way to rephrase the SQL below (not version 2 sql )to avoid duplicate line ? thank you

select c.bf_fund_cd FUND, b.bf_orgn_cd ORG, sum(a.data)
from bf_data a, bf_orgn_cnsl_tbl b, bf_fund_cnsl_tbl c
where
a.bf_orgn_id= b.bf_orgn_id and
a.bf_fund_id = c.bf_fund_id and
a.bf_bdob_id= D.BF_BDOB_CHLD_ID and
a.bf_tm_perd_cd in (select distinct bf_tm_perd_chld_cd
from bf_tm_perd_cnsl_tbl
where bf_tm_perd_select_cd in ('FINALT'))
having sum(a.data) <> 0
group by c.bf_fund_cd,b.bf_orgn_cd

whenriksen SSCarpal Tunnel Points: 4752 More actions · Answer 3

I would start by updating the join syntax. Your duplicate values are almost certainly caused by an incomplete join criteria. I think the newer syntax makes that easier to notice.

It is also helpful to post valid SQL. Your first query has the Having and Group By in the wrong order and I didn't notice a table with the 'D' alias.

Wes
(A solid design is always preferable to a creative workaround)