[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

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
0
kkoehler
Asked:
kkoehler
1 Solution
 
SQL_SERVER_DBACommented:
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'
GROUP BY p.DEP_ID_NUM, p.DEP_ABB_NAM
order by relation DESC
0
 
Christopher KileCommented:
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??

0
 
kkoehlerAuthor Commented:
cpkilekofp:

How do I do that?

karla
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
UnifiedISCommented:

SELECT DISTINCT columns FROM (your existing SQL wrapped in parentheses)

0
 
kkoehlerAuthor Commented:
UnifiedIS:

I am not sure hot to implement that.  I'm currentlly already doing distinct.  Is that an additional select statement?

karla
0
 
Christopher KileCommented:
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.
0
 
webquestsCommented:
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


0
 
kkoehlerAuthor Commented:

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
0
 
kkoehlerAuthor Commented:
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!!!

0
 
Computer101Commented:
PAQed with points refunded (75)

Computer101
EE Admin
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now