Search code examples
sqloracle-databaseduplicatescase-when

How to find duplicate records in oracle CASE WHEN Statement

I have two tables like below. TABLEA and table TABLEB. We do outer join on TRANS_ID and compare both tables.

I want to get the result as shown in result. We check for duplicates in only TABLEA (i.e., based on TABLEA.TRANS_ID).

TABLEA
+------+----------+-------+--+-------------+
| UUID | TRANS_ID | NAME  |  | PHONENUMBER |
+------+----------+-------+--+-------------+
|  123 |     7928 | SCOTT |  | 87482837849 |
|  123 |     7928 | SCOTT |  | 87482837849 |
|  489 |     7493 | TOM   |  | 79274778397 |
|  827 |     7920 | DAVID |  | 23794928749 |
|  324 |     8472 | PAT   |  | 87927478839 |
+------+----------+-------+--+-------------+

TABLEB
+----------+----------+--------+-------------+
| BATCH_ID | TRANS_ID | SENDER | PHONENUMBER |
+----------+----------+--------+-------------+
|   823784 |     7438 | LSK    | 84748394092 |
|   793847 |     7493 | KLI    | 79274778397 |
|   748738 |     7920 | ISL    | 74892890487 |
+----------+----------+--------+-------------+

RESULT
+----------+-------------+-------------------------+
| TRANS_ID | PHONENUMBER |           STATUS        |
+----------+-------------+-------------------------+
|     7928 | 87482837849 |    DUPLICATE RECORD     |
|     7928 | 87482837849 |    NOT IN TABLEB        |
|     7438 | 84748394092 |    NOT IN TABLEA        |
|     7920 | 23794928749 |    PHONENUMBER MISMATCH |
|     8472 | 87927478839 |    NOT IN TABLEB        |
+----------+-------------+-------------------------+

I tried using below case when statement. I got all scenarios covered except for extracting duplicates. When there are duplicate records in TABLE A and they don't exist in TABLEB, the first record should be 'NOT IN TABLEB' and subsequent records should be 'DUPLICATE RECORD'

 CASE
          WHEN TABLEA.TRANS_ID is null THEN 'NOT IN TABLEA'
          WHEN TABLEB.TRANS_ID is null THEN 'NOT IN TABLEB' 
          WHEN decode(TABLEA.PHONENUMBER,ALRAJHI_TABLEB.PHONENUMBER,1,0)=0 THEN 'PHONENUMBER MISMATCH'             
          END
Solution

  • Here's one approach:

    WITH x
     AS (SELECT ROW_NUMBER()
                  OVER (
                    PARTITION BY CASE WHEN a.trans_id IS NULL THEN b.trans_id ELSE a.trans_id END
                    ORDER BY CASE WHEN a.trans_id IS NULL THEN b.trans_id ELSE a.trans_id END) AS rn,
                CASE WHEN a.trans_id IS NULL THEN b.trans_id ELSE a.trans_id END AS trans_id,
                CASE WHEN a.trans_id IS NULL THEN b.phonenumber ELSE a.phonenumber END AS phonenumber,
                CASE
                     WHEN a.trans_id IS NULL THEN 'NOT IN TABLEA'
                     WHEN b.trans_id IS NULL THEN 'NOT IN TABLEB'
                     WHEN DECODE(a.phonenumber, b.phonenumber, 1, 0) = 0 THEN 'PHONENUMBER MISMATCH'
                END AS status
         FROM   tablea a
                FULL OUTER JOIN tableb b
                             ON a.trans_id = b.trans_id),
y AS (
SELECT trans_id,
       phonenumber,
       CASE WHEN rn > 1 THEN 'DUPLICATE RECORD' ELSE status END AS status
FROM   x)
SELECT * FROM y
WHERE  status IS NOT NULL -- Exclude TableA records that are (presumably) OK

    Result

    EDIT: Adjusted query to use second CTE (y) as NOT NULL filter on status was removing rows needed for the ROW_NUMBER() check.