How to change value if multiple duplicate rows more then 3 in Oracle SQL-CodePudding

I am trying to select if A.POLICY_NO or A.POLICY_TITLE values duplicate rows more than 3, want to change C.SMALL_CATEGORY_TITLE values to "Z" and also want to cahge B.SMALL_CATEGORY_SID values to null.

I used 3 tables that YIP.YOUTH_POLICY as A, YIP.YOUTH_POLICY_AREA as B and YIP.YOUTH_SMALL_CATEGORY as C. the PK and FK is POLICY_NO.

ForExample if the table

A.POLICY_NO	B.SMALL_CATEGORY_SID	C.SMALL_CATEGORY_TITLE	A.POLICY_TITLE
1	80	A	VALUE1
1	90	B	VALUE1
1	95	C	VALUE1
2	80	A	VALUE2
2	90	B	VALUE2
2	95	C	VALUE2
3	80	A	VALUE3
3	90	B	VALUE3
4	80	A	VALUE4

I wnat to select like

A.POLICY_NO	B.SMALL_CATEGORY_SID	C.SMALL_CATEGORY_TITLE	A.POLICY_TITLE
1	NULL	Z	VALUE1
2	NULL	Z	VALUE2
3	80	A	VALUE3
3	90	B	VALUE3
4	80	A	VALUE4

this is the query select except when duplicate values more then 3,

SELECT
 A.POLICY_NO
 , B.SMALL_CATEGORY_SID
 , C.SMALL_CATEGORY_TITLE
 , A.POLICY_TITLE
 , COUNT(*) OVER() AS TOTAL_COUNT
FROM
 YIP.YOUTH_POLICY A
 LEFT JOIN
 YIP.YOUTH_POLICY_AREA B
 ON A.POLICY_NO = B.POLICY_NO
 LEFT JOIN
 YIP.YOUTH_SMALL_CATEGORY C
 ON B.SMALL_CATEGORY_SID = C.SMALL_CATEGORY_SID
 WHERE A.POLICY_NO IN (SELECT 
                        F.POLICY_NO 
                        FROM YIP.YOUTH_POLICY F 
                        LEFT JOIN
                         YIP.YOUTH_POLICY_AREA G
                         ON F.POLICY_NO = G.POLICY_NO
                         LEFT JOIN
                         YIP.YOUTH_SMALL_CATEGORY H
                         ON G.SMALL_CATEGORY_SID = H.SMALL_CATEGORY_SID
                        GROUP BY F.POLICY_NO 
                        HAVING COUNT(*) < 3)
ORDER BY A.POLICY_NO;

and I was tried to change C.SMALL_CATEGORY_TITLE values when POLICY_NO values duplicated more then 3

SELECT
 A.POLICY_NO
 --, B.SMALL_CATEGORY_SID
 , SUM(CASE WHEN C.SMALL_CATEGORY_TITLE IN (SELECT 
                                        F.POLICY_NO 
                                        FROM YIP.YOUTH_POLICY F 
                                        LEFT JOIN
                                         YIP.YOUTH_POLICY_AREA G
                                         ON F.POLICY_NO = G.POLICY_NO
                                         LEFT JOIN
                                         YIP.YOUTH_SMALL_CATEGORY H
                                         ON G.SMALL_CATEGORY_SID = H.SMALL_CATEGORY_SID
                                        GROUP BY F.POLICY_NO 
                                        HAVING COUNT(*) > 2) THEN 1 ELSE NULL END) AS 'Z'
 , A.POLICY_TITLE
 , COUNT(*) OVER() AS TOTAL_COUNT
FROM
 YIP.YOUTH_POLICY A
 LEFT JOIN
 YIP.YOUTH_POLICY_AREA B
 ON A.POLICY_NO = B.POLICY_NO
 LEFT JOIN
 YIP.YOUTH_SMALL_CATEGORY C
 ON B.SMALL_CATEGORY_SID = C.SMALL_CATEGORY_SID
ORDER BY A.POLICY_NO;

I got SQL Error [42000]: JDBC-8006:Missing FROM keyword. ¶at line 17, column 59 of null:¶ HAVING COUNT(*) > 2) THEN 1 ELSE NULL END) AS 'Z'¶

Is there any way to fix it? I was thinking more then 5 hours but i could't fix it

CodePudding user response：

The trick is to use the windowing function COUNT(*) OVER to get a count over the entire rowset in order to later make decisions about each individual row. You can finally collapse it down with DISTINCT or GROUP BY.

SELECT DISTINCT
       policy_no,
       CASE WHEN (policy_count >= 3 OR policy_title_count>= 3) THEN NULL 
            ELSE small_category_sid 
       END AS small_category_sid,
       CASE WHEN (policy_count >= 3 OR policy_title_count>= 3) THEN 'Z'
            ELSE small_category_title
       END AS small_category_title,
       policy_title
  FROM (SELECT x.*,
               COUNT(*) OVER (PARTITION BY policy_no) policy_count,
               COUNT(*) OVER (PARTITION BY policy_title) policy_title_count
          FROM yip.youth_policy x)