I am facing a situation to use the same seq num which was associated already to a data when duplicate value found.

SELECT A.VALUE, NVL(B.GROUP_NUM, SEQUENCE_NAME.NEXTVAL)
FROM TABLEA A
INNER JOIN TABLEB B
ON A.ID = B.ID

Mostly TABLEB.GROUP_NUM column holds NULL value. So, Sequence has to be used to generate next value and use it. If duplicate data found in Column 'Value' then use the GROUP_NUM which is already associated instead of generating a new sequence number.

Input data

I am facing a situation to use the same seq num which was associated already to a data when duplicate value found.

SELECT A.VALUE, NVL(B.GROUP_NUM, SEQUENCE_NAME.NEXTVAL)
FROM TABLEA A
INNER JOIN TABLEB B
ON A.ID = B.ID

Mostly TABLEB.GROUP_NUM column holds NULL value. So, Sequence has to be used to generate next value and use it. If duplicate data found in Column 'Value' then use the GROUP_NUM which is already associated instead of generating a new sequence number.

Input data

Expected output.

• sql
• oracle-database
• plsql

• 1 Please add the sample input data for your result – Jonas Metzler Commented Mar 21 at 15:49

3 Answers 3

First detect unique missing values, then get them a sequence number, finally return them including all duplicates:

WITH
    MISSING AS (SELECT DISTINCT B.ID FROM TABLEA A INNER JOIN TABLEB B ON A.ID = B.ID WHERE B.GROUP_NUM IS NULL),
    NEWB AS (SELECT ID, SEQUENCE_NAME.NEXTVAL GROUP_ID FROM MISSING)
SELECT A.VALUE, NVL(B.GROUP_NUM, NEWB.GROUP_NUM)
FROM TABLEA A
INNER JOIN TABLEB B
ON A.ID = B.ID
LEFT JOIN NEWB ON NEWB.ID = B.ID;

Something like this? Get the group num already in use for that value using an analytic function. Simple and concise.

SELECT A.VALUE, NVL(MAX(b.group_num) OVER (PARTITION BY a.value), SEQUENCE_NAME.NEXTVAL) group_num
FROM TABLEA A
INNER JOIN TABLEB B
ON A.ID = B.ID  

But beware that using NVL to conditionally pull from a sequence will cause you to churn through sequences very fast doing this, as the sequence will be incremented even when not used. That's because Oracle will/may evaluate the operands to NVL before passing them to the NVL function. Same with DECODE and CASE and all other conditional expressions. So while you're results will be correct, you'll probably be annoyed at how your sequence advances unnecessarily. If you want to burn sequence numbers only when needed, you'd best write two SQLs, one for each condition, and UNION ALL them together, like this:

SELECT value,
       group_num
  FROM (SELECT A.VALUE, MAX(b.group_num) OVER (PARTITION BY a.value) group_num
          FROM TABLEA A
          INNER JOIN TABLEB B
          ON A.ID = B.ID )
 WHERE group_num IS NOT NULL
UNION ALL
SELECT value,
       SEQUENCE_NAME.NEXTVAL
  FROM (SELECT A.VALUE, MAX(b.group_num) OVER (PARTITION BY a.value) group_num
          FROM TABLEA A
          INNER JOIN TABLEB B
          ON A.ID = B.ID )
 WHERE group_num IS NULL

Or a bit more consolidated:

WITH raw AS (SELECT A.VALUE, MAX(b.group_num) OVER (PARTITION BY a.value) group_num
              FROM TABLEA A
              INNER JOIN TABLEB B
              ON A.ID = B.ID )  
SELECT value,
       group_num
  FROM raw
 WHERE group_num IS NOT NULL
UNION ALL
SELECT value,
       SEQUENCE_NAME.NEXTVAL
  FROM raw
 WHERE group_num IS NULL

Since I don't really see the point to SELECT on SOME_SEQ.NEXTVAL since that will return a different value at each execution, here is a possible solution to actually UPDATE tableB, respecting situations where the GROUP_NUM is already defined but not for all occurrences of the VALUE. (wrap the sequence allocation into a DETERMINISTIC function with value as parameter to avoid increment on each selected row, and use NO_PARALLEL hint to guarantee having the sequence number respecting the global order of VALUE column)

https://dbfiddle.uk/C3XacWEq

merge /*+ WITH_PLSQL */ into tableB dst
using (
    with function snv(value IN number) return number
    deterministic
    is
    begin
        return sequence_name.nextval ;
    end;
    select a.id, d.value, d.group_num from (
        select /*+ no_parallel */ value, nvl(group_num, snv(value)) as group_num
        from (
            select d.value, d.group_num from (
                select value, b.group_num 
                from tableB b 
                    join tableA a on a.id = b.id
                where b.group_num is null
                    and not exists( 
                        select 1 from tableB b1
                            join tableA a1 on a1.id = b1.id
                        where 
                            b1.group_num is not null
                            and a1.value = a.value
                    )
                    
                union
                
                select value, b.group_num 
                from tableB b 
                    join tableA a on a.id = b.id
                where b.group_num is not null
            ) d
        )
        order by value
    ) d
    join tableA a on a.value = d.value
) src
on (src.id = dst.id)
when matched then
    update 
    set group_num = src.group_num
;
/