Dfyjkt

I have the following query:

SELECT 
 F.IID, 
 F.E_NUM AS M_E_NUM, 
 MCI.E_NUM AS MCI_E_NUM, 
 F.C_NUM AS M_C_NUM, 
 MCI.C_NUM AS MCI_C_NUM,
 F.ET_ID AS M_ET_ID, 
 EDIE.ET_ID AS ED_INDV_ET_ID,
 COUNT(*) OVER (PARTITION BY F.IID) IID_COUNT
FROM FT_T F JOIN CEMEI_T MCI ON F.IID = MCI.IID
 JOIN EDE_T EDE ON MCI.E_NUM = EDE.E_NUM
 JOIN EDIE_T EDIE ON EDIE.IID = F.IID AND EDIE.ET_ID = EDE.ET_ID
WHERE 
 F.DEL_F = 'N'
 AND MCI.EFF_END_DT IS NULL
 AND MCI.TOS = 'BVVB'
 AND EDE.PTEND_DT IS NULL
 AND EDE.DEL_S = 'N'
 AND EDE.CUR_IND = 'A'
 AND EDIE.TAR_N = 'Y'
 AND F.IID IN
 (
 SELECT DISTINCT IID 
 FROM FT_T 
 WHERE GROUP_ID = 'BG' 
 AND DEL_F = 'N' 
 AND (IID, E_NUM) NOT IN 
 (
 SELECT IID, E_NUM FROM CEMEI_T 
 WHERE TOS = 'BVVB' AND EFF_END_DT IS NULL
 )
 );

I am basically grabbing information from several tables and creating a flat record of them.

Everything works accordingly except now I need to find out whether there are two records in FT_T table with identical IID's and display that count as part of the result set.

I tried to use partitioning but all the rows in the result set return a single count even though there are ones that have 2 records with identical IID's in FT_T.

The reason I initially said that I'm gathering information from several tables is due to the fact that FT_T might not have all the information I need if two records are not available for the same IID, so I have to retrieve them from other tables JOINed in the query. However, I need to know which FT_T.IID's have two records in FT_T (or greater than one).

Perhaps you need to calculate the count before the join and filtering:

SELECT . . .
FROM (SELECT F.*,
 COUNT(*) OVER (PARTITION BY F.IID) as IID_CNT
 FROM FT_T F
 ) JOIN
 CEMEI_T MCI
 ON F.IID = MCI.IID JOIN
 EDE_T EDE
 ON MCI.E_NUM = EDE.E_NUM JOIN
 EDIE_T EDIE
 ON EDIE.IID = F.IID AND EDIE.ET_ID = EDE.ET_ID
 . . .

this is merely a comment/observation, but formatting is needed

You use of in(...) with select distinct and not in(...,...) seems complex and could be a problem if some values are NULL. I suggest you consider using EXISTS and NOT EXISTS instead. e.g.

AND EXISTS (
 SELECT
 NULL
 FROM FT_T
 WHERE F.IID = FT_T.IID
 AND FT_T.GROUP_ID = 'BG'
 AND FT_T.DEL_F = 'N'
 AND NOT EXISTS (
 SELECT
 NULL
 FROM CEMEI_T
 WHERE FT_T.IID = CEMEI_T.IID
 AND FT_T.E_NUM = CEMEI_T.E_NUM
 AND CEMEI_T.TOS = 'BVVB'
 AND CEMEI_T.EFF_END_DT IS NULL
 )
)