SQL query returning incorrect results
I have this long SQL query that is returning an unusual result. It is duplicating one of the results even thoug it states that it is distinct. It works for the other scenarions but not for the one that ouputs as DEN:
This is my query:
Select distinct p.DEP_ID_NUM, p.DEP_ABB_NAM, CASE WHEN e.DEJ_DPND_REL_COD = 'N' THEN 'Spouse/Partner'
WHEN e.DEJ_DPND_REL_COD = 'S' THEN 'Spouse/Partner' WHEN e.DEJ_DPND_REL_COD = 'P' THEN 'Spouse/Partner'
WHEN e.DEJ_DPND_REL_COD = 'D' THEN 'Child' WHEN e.DEJ_DPND_REL_COD = 'Y' THEN 'Child'
WHEN e.DEJ_DPND_REL_COD = 'Z' THEN 'Child' END AS relation,
CASE WHEN e.DEJ_DPND_REL_COD = 'S' and (p.DEP_SMK_COD = 'Y' or p.DEP_SMK_COD = '') then 'Yes'
WHEN e.DEJ_DPND_REL_COD = 'S' and p.DEP_SMK_COD = 'N' then 'No'
WHEN e.DEJ_DPND_REL_COD = 'N' and (p.DEP_SMK_COD = 'Y' or p.DEP_SMK_COD = '') then 'Yes'
WHEN e.DEJ_DPND_REL_COD = 'N' and p.DEP_SMK_COD = 'N' then 'No'
WHEN e.DEJ_DPND_REL_COD = 'P' and (p.DEP_SMK_COD = 'Y' or p.DEP_SMK_COD = '') then 'Yes'
WHEN e.DEJ_DPND_REL_COD = 'P' and p.DEP_SMK_COD = 'N' then 'No'
WHEN e.DEJ_DPND_REL_COD = 'D' THEN 'N/A' WHEN e.DEJ_DPND_REL_COD = 'Y' THEN 'N/A' WHEN e.DEJ_DPND_REL_COD = 'Z' THEN 'N/A'
ELSE c.COV_SMK_COD END AS smoke,
(Select distinct c.COV_CVRG_GRP_COD from DBO.IAERCEM i
inner join DBO.Coverage c on i.I_IAERCEM = c.I_IAERCEM inner join DBO.DEP_COV_S d on d.I_Coverage = c.I_coverage
inner join DBO.DEPENDENT p2 on p2.I_DEPENDENT = d.I_DEPENDENT
where i.M100_ID = '1234' and c.cov_cvrg_beg_dat_intl >= '2008-01-01'
and p2.DEP_ID_NUM = p.DEP_ID_NUM and c.COV_CVRG_GRP_COD = '90' ) as MED,
(Select distinct c.COV_CVRG_GRP_COD from DBO.IAERCEM i inner join DBO.Coverage c on i.I_IAERCEM = c.I_IAERCEM
inner join DBO.DEP_COV_S d on d.I_Coverage = c.I_coverage inner join DBO.DEPENDENT p2 on p2.I_DEPENDENT = d.I_DEPENDENT
where i.M100_ID = '1234' and p2.DEP_ID_NUM = p.DEP_ID_NUM and c.cov_cvrg_end_dat_intl = '9999-12-31'
and c.COV_CVRG_GRP_COD = '80' ) as DEN,
(Select distinct c.COV_CVRG_GRP_COD from DBO.IAERCEM i inner join DBO.Coverage c on i.I_IAERCEM = c.I_IAERCEM
inner join DBO.DEP_COV_S d on d.I_Coverage = c.I_coverage inner join DBO.DEPENDENT p2 on p2.I_DEPENDENT = d.I_DEPENDENT
where i.M100_ID = '1234' and c.cov_cvrg_beg_dat_intl >= '2008-01-01' and
p2.DEP_ID_NUM = p.DEP_ID_NUM and c.COV_CVRG_GRP_COD = '50' ) as VOL,
(Select distinct c.COV_CVRG_GRP_COD from DBO.IAERCEM i inner join DBO.Coverage c on i.I_IAERCEM = c.I_IAERCEM
inner join DBO.DEP_COV_S d on d.I_Coverage = c.I_coverage inner join DBO.DEPENDENT p2 on p2.I_DEPENDENT = d.I_DEPENDENT
where i.M100_ID = '1234' and c.cov_cvrg_end_dat_intl = '9999-12-31' and
p2.DEP_ID_NUM = p.DEP_ID_NUM and c.COV_CVRG_GRP_COD = '20' ) as LTC from DBO.IAERCEM i
inner join DBO.Coverage c on i.I_IAERCEM = c.I_IAERCEM inner join DBO.DEP_COV_S d on d.I_Coverage = c.I_coverage
inner join DBO.DEPENDENT p on p.I_DEPENDENT = d.I_DEPENDENT inner join DBO.DEP_DEJ_S j on p.I_DEPENDENT = j.I_DEPENDENT
inner join DBO.DEPEM_JCT e on j.I_DEPEM_JCT = e.I_DEPEM_JCT where i.M100_ID = '1234' and e.DEJ_DPND_REL_COD <> 'X'
and e.DEJ_DPND_REL_COD <> 'R' and e.DEJ_DPND_REL_COD <> 'GC' and e.DEJ_DPND_REL_COD <> 'XD' and e.DEJ_DPND_REL_COD <> 'XP'
order by relation DESC
this is the result, notice how the first person is duplicated. :
DEP_ID_NUM DEP_ABB_NAM RELATION SMOKE MED DEN VOL LTC
1234 Miremadi, Fatemeh Spouse/Partner No 90
1234 Miremadi, Fatemeh Spouse/Partner No 90
5678 Nouri, Kian Child N/A 90 80
Any help weill be appreciated
Karla
This is my query:
Select distinct p.DEP_ID_NUM, p.DEP_ABB_NAM, CASE WHEN e.DEJ_DPND_REL_COD = 'N' THEN 'Spouse/Partner'
WHEN e.DEJ_DPND_REL_COD = 'S' THEN 'Spouse/Partner' WHEN e.DEJ_DPND_REL_COD = 'P' THEN 'Spouse/Partner'
WHEN e.DEJ_DPND_REL_COD = 'D' THEN 'Child' WHEN e.DEJ_DPND_REL_COD = 'Y' THEN 'Child'
WHEN e.DEJ_DPND_REL_COD = 'Z' THEN 'Child' END AS relation,
CASE WHEN e.DEJ_DPND_REL_COD = 'S' and (p.DEP_SMK_COD = 'Y' or p.DEP_SMK_COD = '') then 'Yes'
WHEN e.DEJ_DPND_REL_COD = 'S' and p.DEP_SMK_COD = 'N' then 'No'
WHEN e.DEJ_DPND_REL_COD = 'N' and (p.DEP_SMK_COD = 'Y' or p.DEP_SMK_COD = '') then 'Yes'
WHEN e.DEJ_DPND_REL_COD = 'N' and p.DEP_SMK_COD = 'N' then 'No'
WHEN e.DEJ_DPND_REL_COD = 'P' and (p.DEP_SMK_COD = 'Y' or p.DEP_SMK_COD = '') then 'Yes'
WHEN e.DEJ_DPND_REL_COD = 'P' and p.DEP_SMK_COD = 'N' then 'No'
WHEN e.DEJ_DPND_REL_COD = 'D' THEN 'N/A' WHEN e.DEJ_DPND_REL_COD = 'Y' THEN 'N/A' WHEN e.DEJ_DPND_REL_COD = 'Z' THEN 'N/A'
ELSE c.COV_SMK_COD END AS smoke,
(Select distinct c.COV_CVRG_GRP_COD from DBO.IAERCEM i
inner join DBO.Coverage c on i.I_IAERCEM = c.I_IAERCEM inner join DBO.DEP_COV_S d on d.I_Coverage = c.I_coverage
inner join DBO.DEPENDENT p2 on p2.I_DEPENDENT = d.I_DEPENDENT
where i.M100_ID = '1234' and c.cov_cvrg_beg_dat_intl >= '2008-01-01'
and p2.DEP_ID_NUM = p.DEP_ID_NUM and c.COV_CVRG_GRP_COD = '90' ) as MED,
(Select distinct c.COV_CVRG_GRP_COD from DBO.IAERCEM i inner join DBO.Coverage c on i.I_IAERCEM = c.I_IAERCEM
inner join DBO.DEP_COV_S d on d.I_Coverage = c.I_coverage inner join DBO.DEPENDENT p2 on p2.I_DEPENDENT = d.I_DEPENDENT
where i.M100_ID = '1234' and p2.DEP_ID_NUM = p.DEP_ID_NUM and c.cov_cvrg_end_dat_intl = '9999-12-31'
and c.COV_CVRG_GRP_COD = '80' ) as DEN,
(Select distinct c.COV_CVRG_GRP_COD from DBO.IAERCEM i inner join DBO.Coverage c on i.I_IAERCEM = c.I_IAERCEM
inner join DBO.DEP_COV_S d on d.I_Coverage = c.I_coverage inner join DBO.DEPENDENT p2 on p2.I_DEPENDENT = d.I_DEPENDENT
where i.M100_ID = '1234' and c.cov_cvrg_beg_dat_intl >= '2008-01-01' and
p2.DEP_ID_NUM = p.DEP_ID_NUM and c.COV_CVRG_GRP_COD = '50' ) as VOL,
(Select distinct c.COV_CVRG_GRP_COD from DBO.IAERCEM i inner join DBO.Coverage c on i.I_IAERCEM = c.I_IAERCEM
inner join DBO.DEP_COV_S d on d.I_Coverage = c.I_coverage inner join DBO.DEPENDENT p2 on p2.I_DEPENDENT = d.I_DEPENDENT
where i.M100_ID = '1234' and c.cov_cvrg_end_dat_intl = '9999-12-31' and
p2.DEP_ID_NUM = p.DEP_ID_NUM and c.COV_CVRG_GRP_COD = '20' ) as LTC from DBO.IAERCEM i
inner join DBO.Coverage c on i.I_IAERCEM = c.I_IAERCEM inner join DBO.DEP_COV_S d on d.I_Coverage = c.I_coverage
inner join DBO.DEPENDENT p on p.I_DEPENDENT = d.I_DEPENDENT inner join DBO.DEP_DEJ_S j on p.I_DEPENDENT = j.I_DEPENDENT
inner join DBO.DEPEM_JCT e on j.I_DEPEM_JCT = e.I_DEPEM_JCT where i.M100_ID = '1234' and e.DEJ_DPND_REL_COD <> 'X'
and e.DEJ_DPND_REL_COD <> 'R' and e.DEJ_DPND_REL_COD <> 'GC' and e.DEJ_DPND_REL_COD <> 'XD' and e.DEJ_DPND_REL_COD <> 'XP'
order by relation DESC
this is the result, notice how the first person is duplicated. :
DEP_ID_NUM DEP_ABB_NAM RELATION SMOKE MED DEN VOL LTC
1234 Miremadi, Fatemeh Spouse/Partner No 90
1234 Miremadi, Fatemeh Spouse/Partner No 90
5678 Nouri, Kian Child N/A 90 80
Any help weill be appreciated
Karla
There is absolutely no way to tell this without looking at your data or without doing a detailed analysis of every reference in your query for multiple returns.
Why don't you just make the whole thing a SELECT DISTINCT??
Why don't you just make the whole thing a SELECT DISTINCT??
ASKER
cpkilekofp:
How do I do that?
karla
How do I do that?
karla
SELECT DISTINCT columns FROM (your existing SQL wrapped in parentheses)
ASKER
UnifiedIS:
I am not sure hot to implement that. I'm currentlly already doing distinct. Is that an additional select statement?
karla
I am not sure hot to implement that. I'm currentlly already doing distinct. Is that an additional select statement?
karla
I believe the problem is in your data, at least from the appearance of the report section you show us. The two first records do NOT appear to be identical:
DEP_ID_NUM DEP_ABB_NAM RELATION SMOKE MED DEN VOL LTC
1234 Miremadi, Fatemeh Spouse/Partner No 90
1234 Miremadi, Fatemeh Spouse/Partner No 90
DEP_ABB_NAME for the first record has four additional spaces at the beginning of it, or DEP_ID_NUM for that record has four additional spaces at the end of it, one of the two. Thus, they are distinct from each other.
DEP_ID_NUM DEP_ABB_NAM RELATION SMOKE MED DEN VOL LTC
1234 Miremadi, Fatemeh Spouse/Partner No 90
1234 Miremadi, Fatemeh Spouse/Partner No 90
DEP_ABB_NAME for the first record has four additional spaces at the beginning of it, or DEP_ID_NUM for that record has four additional spaces at the end of it, one of the two. Thus, they are distinct from each other.
Agreed, you can use the following script to quickly fix this issue:
update yourtable
set DEP_ABB_NAM = replace(DEP_ABB_NAM,' Miremadi, Fatemeh',' Miremadi, Fatemeh')
or you can edit the field in enterprise manager:
select table and then open / open all rows
update yourtable
set DEP_ABB_NAM = replace(DEP_ABB_NAM,' Miremadi, Fatemeh',' Miremadi, Fatemeh')
or you can edit the field in enterprise manager:
select table and then open / open all rows
ASKER
I had also thought that there was a difference in the data, but that's not it. I did a lenght of the fields and the two repeated lines are exactly the same, which is why this is so troubling. It looks like it has more spaces because of the way it pasted here, but they don't.
Karla
ASKER
I finally figured it out, there was a condition missing from the main select statement: and c.cov_cvrg_beg_dat_intl >= '2008-01-01' as soon as i added it worked, yeahhhhh!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
WHEN e.DEJ_DPND_REL_COD = 'S' THEN 'Spouse/Partner' WHEN e.DEJ_DPND_REL_COD = 'P' THEN 'Spouse/Partner'
WHEN e.DEJ_DPND_REL_COD = 'D' THEN 'Child' WHEN e.DEJ_DPND_REL_COD = 'Y' THEN 'Child'
WHEN e.DEJ_DPND_REL_COD = 'Z' THEN 'Child' END AS relation,
CASE WHEN e.DEJ_DPND_REL_COD = 'S' and (p.DEP_SMK_COD = 'Y' or p.DEP_SMK_COD = '') then 'Yes'
WHEN e.DEJ_DPND_REL_COD = 'S' and p.DEP_SMK_COD = 'N' then 'No'
WHEN e.DEJ_DPND_REL_COD = 'N' and (p.DEP_SMK_COD = 'Y' or p.DEP_SMK_COD = '') then 'Yes'
WHEN e.DEJ_DPND_REL_COD = 'N' and p.DEP_SMK_COD = 'N' then 'No'
WHEN e.DEJ_DPND_REL_COD = 'P' and (p.DEP_SMK_COD = 'Y' or p.DEP_SMK_COD = '') then 'Yes'
WHEN e.DEJ_DPND_REL_COD = 'P' and p.DEP_SMK_COD = 'N' then 'No'
WHEN e.DEJ_DPND_REL_COD = 'D' THEN 'N/A' WHEN e.DEJ_DPND_REL_COD = 'Y' THEN 'N/A' WHEN e.DEJ_DPND_REL_COD = 'Z' THEN 'N/A'
ELSE c.COV_SMK_COD END AS smoke,
(Select distinct c.COV_CVRG_GRP_COD from DBO.IAERCEM i
inner join DBO.Coverage c on i.I_IAERCEM = c.I_IAERCEM inner join DBO.DEP_COV_S d on d.I_Coverage = c.I_coverage
inner join DBO.DEPENDENT p2 on p2.I_DEPENDENT = d.I_DEPENDENT
where i.M100_ID = '1234' and c.cov_cvrg_beg_dat_intl >= '2008-01-01'
and p2.DEP_ID_NUM = p.DEP_ID_NUM and c.COV_CVRG_GRP_COD = '90' ) as MED,
(Select distinct c.COV_CVRG_GRP_COD from DBO.IAERCEM i inner join DBO.Coverage c on i.I_IAERCEM = c.I_IAERCEM
inner join DBO.DEP_COV_S d on d.I_Coverage = c.I_coverage inner join DBO.DEPENDENT p2 on p2.I_DEPENDENT = d.I_DEPENDENT
where i.M100_ID = '1234' and p2.DEP_ID_NUM = p.DEP_ID_NUM and c.cov_cvrg_end_dat_intl = '9999-12-31'
and c.COV_CVRG_GRP_COD = '80' ) as DEN,
(Select distinct c.COV_CVRG_GRP_COD from DBO.IAERCEM i inner join DBO.Coverage c on i.I_IAERCEM = c.I_IAERCEM
inner join DBO.DEP_COV_S d on d.I_Coverage = c.I_coverage inner join DBO.DEPENDENT p2 on p2.I_DEPENDENT = d.I_DEPENDENT
where i.M100_ID = '1234' and c.cov_cvrg_beg_dat_intl >= '2008-01-01' and
p2.DEP_ID_NUM = p.DEP_ID_NUM and c.COV_CVRG_GRP_COD = '50' ) as VOL,
(Select distinct c.COV_CVRG_GRP_COD from DBO.IAERCEM i inner join DBO.Coverage c on i.I_IAERCEM = c.I_IAERCEM
inner join DBO.DEP_COV_S d on d.I_Coverage = c.I_coverage inner join DBO.DEPENDENT p2 on p2.I_DEPENDENT = d.I_DEPENDENT
where i.M100_ID = '1234' and c.cov_cvrg_end_dat_intl = '9999-12-31' and
p2.DEP_ID_NUM = p.DEP_ID_NUM and c.COV_CVRG_GRP_COD = '20' ) as LTC from DBO.IAERCEM i
inner join DBO.Coverage c on i.I_IAERCEM = c.I_IAERCEM inner join DBO.DEP_COV_S d on d.I_Coverage = c.I_coverage
inner join DBO.DEPENDENT p on p.I_DEPENDENT = d.I_DEPENDENT inner join DBO.DEP_DEJ_S j on p.I_DEPENDENT = j.I_DEPENDENT
inner join DBO.DEPEM_JCT e on j.I_DEPEM_JCT = e.I_DEPEM_JCT where i.M100_ID = '1234' and e.DEJ_DPND_REL_COD <> 'X'
and e.DEJ_DPND_REL_COD <> 'R' and e.DEJ_DPND_REL_COD <> 'GC' and e.DEJ_DPND_REL_COD <> 'XD' and e.DEJ_DPND_REL_COD <> 'XP'
GROUP BY p.DEP_ID_NUM, p.DEP_ABB_NAM
order by relation DESC