Show duplicate NI Numbers

Hi again

I have this code which tells me if there are any duplicate NI numbers:

 with
cte_lv
as
(
select 
e.PERSON_REF,
e.EMPLOYEE_NUMBER,
coalesce(rtrim(ltrim(known_as)),per.FIRST_FORNAME)  as FORENAME,
per.SURNAME as SURNAME,
count (per.NI_NO) as NINO,
per.INITIALS INITS,
per.TITLE TITLE,
post.number_R as POST_NUMBER,
post.long_desc as POST_LONG_DESC,
e.START_DATE EMPSTARTDATE,
e.END_DATE EMPENDDATE,
CGrp.ID CGRPID,
CGrp.LONG_DESC CGRPDESC,
PGrp.ID PGRPID,
PGrp.LONG_DESC PGRPDESC,
narr_occtype.long_desc as OCC_TYPE,
narr_joinreason.long_desc as POST_JOIN_REASON,
narr_joinreason2.long_desc as EMP_JOIN_REASON,
ph.projected_end_date
from D550M e
left outer join D500M per on e.PERSON_REF = per.PERSON_REF
left outer join D100M CGrp on e.LEVEL1_PAY_STR_REF = CGrp.REF
left outer join D100M PGrp on e.PAY_STR_REF = PGrp.REF 
left outer join D580M ph on e.PERSON_REF = ph.PERSON_REF and
( ph.start_date <= sysdate and (ph.end_date is null or ph.end_Date >= sysdate))
left outer join D200M post on ph.ref  = post.ref
left outer join D800M narr_occtype on ph.occ_type = narr_occtype.narrative_code and narr_occtype.narrative_category = 'OCCTYP'
left outer join D800M narr_joinreason on ph.join_reason= narr_joinreason.narrative_code and narr_joinreason.narrative_category = 'RSNCHG'
left outer join D800M narr_joinreason2 on e.orig_how_joined = narr_joinreason2.narrative_code and narr_joinreason2.narrative_category = 'RSNCHG'


),

CTE_PostGrade AS
(
select e.PERSON_REF,
Gr.NUMBER_R GRADE
from D550M e
left join D580M ph on e.PERSON_REF = ph.PERSON_REF
left join D554M phGr on phGr.PERSON_REF = ph.PERSON_REF and
phGr.POST_REF = ph.REF
left join D400M Gr on Gr.REF = phGr.REF
where ph.MAIN_FLAG = 'Y'
and e.START_DATE <= trunc(SYSDATE) and coalesce(e.END_DATE, trunc(SYSDATE)) >= trunc(SYSDATE)
and ph.START_DATE <= trunc(SYSDATE) and coalesce(ph.END_DATE, trunc(SYSDATE)) >= trunc(SYSDATE)
and phGr.START_DATE <= trunc(SYSDATE) and coalesce(phGr.END_DATE, trunc(SYSDATE)) >= trunc(SYSDATE)
)
select  
*
count (NINO) as nino

--sysdate
from cte_lv

GROUP BY
NINO
HAVING
(Count(NINO) > 1)

Open in new window


This is fine, but i also need to display the employee ref next to the count. Its no good knowing that 2 employees have the same NI number if i cannot identify those employees.

I have tried to display the EMPLOYE£E REF next to the count, but get the below error:

NOT A SINGLE-GROUP GROUPFUNCTION

Any ideas?

Thanks,
Rich
Richiep86Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeOM_DBACommented:
Try:
WITH Cte_Lv
     AS (SELECT E.Person_Ref
              , E.Employee_Number
              , COALESCE ( RTRIM ( LTRIM ( Known_As ) ), Per.First_Forname )
                   AS Forename
              , Per.Surname AS Surname
              , Per.Ni_No AS Nino
              , Per.Initials Inits
              , Per.Title Title
              , POST.Number_R AS Post_Number
              , POST.Long_Desc AS Post_Long_Desc
              , E.Start_Date Empstartdate
              , E.End_Date Empenddate
              , Cgrp.Id Cgrpid
              , Cgrp.Long_Desc Cgrpdesc
              , Pgrp.Id Pgrpid
              , Pgrp.Long_Desc Pgrpdesc
              , Narr_Occtype.Long_Desc AS Occ_Type
              , Narr_Joinreason.Long_Desc AS Post_Join_Reason
              , Narr_Joinreason2.Long_Desc AS Emp_Join_Reason
              , Ph.Projected_End_Date
           FROM D550m E
                LEFT OUTER JOIN D500m Per ON E.Person_Ref = Per.Person_Ref
                LEFT OUTER JOIN D100m Cgrp ON E.Level1_Pay_Str_Ref = Cgrp.REF
                LEFT OUTER JOIN D100m Pgrp ON E.Pay_Str_Ref = Pgrp.REF
                LEFT OUTER JOIN D580m Ph
                   ON E.Person_Ref = Ph.Person_Ref
                  AND ( Ph.Start_Date <= SYSDATE
                    AND ( Ph.End_Date IS NULL OR Ph.End_Date >= SYSDATE ) )
                LEFT OUTER JOIN D200m POST ON Ph.REF = POST.REF
                LEFT OUTER JOIN D800m Narr_Occtype
                   ON Ph.Occ_Type = Narr_Occtype.Narrative_Code
                  AND Narr_Occtype.Narrative_Category = 'OCCTYP'
                LEFT OUTER JOIN D800m Narr_Joinreason
                   ON Ph.Join_Reason = Narr_Joinreason.Narrative_Code
                  AND Narr_Joinreason.Narrative_Category = 'RSNCHG'
                LEFT OUTER JOIN D800m Narr_Joinreason2
                   ON E.Orig_How_Joined = Narr_Joinreason2.Narrative_Code
                  AND Narr_Joinreason2.Narrative_Category = 'RSNCHG')
   , Cte_Postgrade
     AS (SELECT E.Person_Ref, Gr.Number_R Grade
           FROM D550m E
                LEFT JOIN D580m Ph ON E.Person_Ref = Ph.Person_Ref
                LEFT JOIN D554m Phgr
                   ON Phgr.Person_Ref = Ph.Person_Ref
                  AND Phgr.Post_Ref = Ph.REF
                LEFT JOIN D400m Gr ON Gr.REF = Phgr.REF
          WHERE Ph.Main_Flag = 'Y'
            AND E.Start_Date <= TRUNC ( SYSDATE )
            AND COALESCE ( E.End_Date, TRUNC ( SYSDATE ) ) >= TRUNC ( SYSDATE )
            AND Ph.Start_Date <= TRUNC ( SYSDATE )
            AND COALESCE ( Ph.End_Date, TRUNC ( SYSDATE ) ) >=
                   TRUNC ( SYSDATE )
            AND Phgr.Start_Date <= TRUNC ( SYSDATE )
            AND COALESCE ( Phgr.End_Date, TRUNC ( SYSDATE ) ) >=
                   TRUNC ( SYSDATE ))
  SELECT Person_Ref, COUNT ( Nino ) AS Nino
    FROM Cte_Lv
GROUP BY Person_Ref
  HAVING ( COUNT ( Nino ) > 1 )

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richiep86Author Commented:
Perfect - thankyou very much :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.