CODEWITHSUNDEEP
sqloracle-database
Kaushal Talniya
Kaushal Talniya

Reputation: 192

Duplicate value of a field with respect to other field

I want to get duplicate value of a field with respect to other field. duplicate value of RAP07C.SSISEQ on RAP07C.SSITYP

I used 'HAVING COUNT(RAP07C.SSISEQ) > 1' with respect to RAP07C.SSITYP, But I am getting the records if they have two or more records even though the two value of RAP07C.SSISEQ is same with respect to RAP07C.SSITYP. But I want records with the different value of RAP07C.SSISEQ.

SELECT DISTINCT 
  /*+ use_hash(RAP01 rap07a RAP07C) */
  Rap01.plcy,
  Rap01.j01_PT_LINE_cat_Cd AS j01_PT_LINE_cat_Cd,
  Rap01.J01_Pt_State_Cd    AS J01_Pt_State_Cd,
  COUNT(RAP07C.SSISEQ)     AS Count_of_SSISEQ,
  --RAP07C.SSISEQ,
  RAP07C.SSITYP
  --
FROM Rap01
  --
JOIN RAP07C
ON RAP07C.J41_PT_LINE_CAT_CD  = rap01.j01_pt_line_cat_cd
AND RAP07C.J41_PT_CDB_PART_ID = rap01.j01_pt_cdb_part_id
AND RAP07C.J41_PT_STATE_CD    = rap01.j01_pt_state_cd
AND RAP07C.PLCY               = rap01.plcy
AND RAP07C.dropdt_t           = '31-DEC-9999'
  --
WHERE Rap01.Line3 IN ('010','016')
GROUP BY RAP01.plcy,
  j01_PT_LINE_cat_Cd,
  J01_Pt_State_Cd,
  RAP07C.SSITYP
HAVING COUNT(RAP07C.SSISEQ) > 1;

I want different values of RAP07C.SSISEQ with respect to RAP07C.SSITYP Like: IFS - 001 IFS - 001 as I got above two values 001 with respect to IFS, But I need to get only different values for IFS (RAP07C.SSITYP) as IFS - 001 IFS - 002.

Upvotes: 0

Views: 42

Answers (1)

user11357152
user11357152

Reputation: 11

Try putting the word DISTINCT inside the COUNT.

SELECT 
  Rap01.plcy,
  Rap01.j01_PT_LINE_cat_Cd AS j01_PT_LINE_cat_Cd,
  Rap01.J01_Pt_State_Cd    AS J01_Pt_State_Cd,
  COUNT(DISTINCT RAP07C.SSISEQ)     AS Count_of_SSISEQ,
  RAP07C.SSITYP

FROM Rap01

JOIN RAP07C
ON RAP07C.J41_PT_LINE_CAT_CD  = rap01.j01_pt_line_cat_cd
AND RAP07C.J41_PT_CDB_PART_ID = rap01.j01_pt_cdb_part_id
AND RAP07C.J41_PT_STATE_CD    = rap01.j01_pt_state_cd
AND RAP07C.PLCY               = rap01.plcy
AND RAP07C.dropdt_t           = '31-DEC-9999'

WHERE Rap01.Line3 IN ('010','016')
GROUP BY RAP01.plcy,
  j01_PT_LINE_cat_Cd,
  J01_Pt_State_Cd,
  RAP07C.SSITYP
HAVING COUNT(DISTINCT RAP07C.SSISEQ) > 1;

Upvotes: 1

Related Questions