How to duplicate a row with a condition

Question

I have a data set:

SELECT '1' ID, '30' CurrQty, '100' ReqQty FROM dual
UNION ALL SELECT '1' ID, '30' CurrQty, '100' ReqQty FROM dual
UNION ALL SELECT '1' ID, '20' CurrQty, '100' ReqQty FROM dual
UNION ALL SELECT '2' ID, '100' CurrQty, '100' ReqQty FROM dual

ID CURRQTY REQQTY
-- ------- ------
1  30      100   
1  30      100   
1  20      100   
2  100     100   

I would like to get something like this:

ID | CurrQty | ReqQty
1  |   30    | 100
1  |   30    | 100
1  |   20    | 100
1  |   20    | 100   - created a new line ReqQty - Sum(CurrQty)
2  |  100    | 100

If Sum(CurrQty) for same Id is smaller than ReqQty, then create a new line with different ReqQty - Sum(CurrQty).

I tried something like this, but it created a duplicate for each line, and I want to create only 1 new line within the Id group:

WITH levels AS (
   SELECT LEVEL AS lvl
   FROM DUAL
   CONNECT BY LEVEL <= 2)
   SELECT * FROM (
SELECT id, CurrQty, ReqQty, SUM (CurrQty) OVER (PARTITION BY ID) AS sum_for_id
FROM (
SELECT '1' ID, '30' CurrQty, '100' ReqQty FROM dual
UNION ALL SELECT '1' ID, '30' CurrQty, '100' ReqQty FROM dual
UNION ALL SELECT '1' ID, '20' CurrQty, '100' ReqQty FROM dual
UNION ALL SELECT '2' ID, '100' CurrQty, '100' ReqQty FROM dual
)) INNER JOIN LEVELS ON (lvl = 1 OR sum_for_id <ReqQty)

ID CURRQTY REQQTY SUM_FOR_ID        LVL
-- ------- ------ ---------- ----------
1  30      100            80          1
1  30      100            80          1
1  20      100            80          1
2  100     100           100          1
1  30      100            80          2
1  30      100            80          2
1  20      100            80          2

Answer 1

Oddly, in the output you are not flagging out the "added" rows - how are you going to distinguish them from the original ones, either on a report or in further processing?

Anyway, that's none of my business. Then: Why are quantities strings and not numbers? That really makes no sense; in my simulated data, I changed that.

You didn't say what you want to have happen when reqqty is less than the sum of currqty, nor how any possible null should be handled. I assumed there is no null anywhere, and if reqqty is less than the sum of currqty, you don't want anything done for that customer (other than outputting the original rows).

Is reqqty the same in all rows for a given customer? That's the only way your question makes sense; but if what you show is a base table (not the result of some join) then your table violates Third Normal Form. You may want to consider that.

Here is a very simple and quick way to do what you need. I didn't order the output, since you didn't say much about that.

with
  dataset(id, currqty, reqqty) as (
    select '1',  30, 100 from dual union all
    select '1',  30, 100 from dual union all
    select '1',  20, 100 from dual union all
    select '2', 100, 100 from dual
  )
select     id, currqty, reqqty
  from     dataset
union all
select     id, min(reqqty) - sum(currqty) as currqty, min(reqqty) as reqqty
  from     dataset
  group by id
  having   sum(currqty) < min(reqqty)
-- ORDER BY <whatever>; ORDER BY applies to the result of UNION ALL.
;

ID    CURRQTY     REQQTY
-- ---------- ----------
1          30        100
1          30        100
1          20        100
2         100        100
1          20        100

Answer 2

You could do this is in two stages. First you can see if you have reached the requirement with an analytic sum function:

-- CTE for sample data
with your_table (id, currqty, reqqty) as (
            select 1, 30, 100 from dual
  union all select 1, 30, 100 from dual
  union all select 1, 20, 100 from dual
  union all select 2, 100, 100 from dual
)
-- actual query
select id, currqty, reqqty,
  sum(currqty) over (partition by id) as totalqty
from your_table;

ID    CURRQTY     REQQTY   TOTALQTY
-- ---------- ---------- ----------
 1         30        100         80
 1         30        100         80
 1         20        100         80
 2        100        100        100

and then use that as a CTE, adding a second analytic function to pick any row for each ID to use as the basis for the extra one (where needed), and unioning two queries together:

with your_table (id, currqty, reqqty) as (
            select 1, 30, 100 from dual
  union all select 1, 30, 100 from dual
  union all select 1, 20, 100 from dual
  union all select 2, 100, 100 from dual
),
cte (id, currqty, reqqty, totalqty, rn) as (
  select id, currqty, reqqty,
    sum(currqty) over (partition by id),
    row_number() over (partition by id order by null)
  from your_table
)
select id, currqty, reqqty
from cte
union all
select id, reqqty - totalqty, reqqty
from cte
where totalqty < reqqty
and rn = 1;

ID    CURRQTY     REQQTY
-- ---------- ----------
 1         30        100
 1         30        100
 1         20        100
 2        100        100
 1         20        100

The first branch of the union just gets your original data, via the CTE. The second branch only looks for one row for any ID where the total doesn't match the requirement, and generates a new row for that ID for the difference.

If you don't want to use a second analytic function you could use a distinct query instead:

...
select id, currqty, reqqty
from cte
union all
select distinct id, reqqty - totalqty, reqqty
from cte
where totalqty < reqqty;

Incidentally, I've assumed your values are all actually numbers rather than strings; storing quantities or numeric IDs, or any other numeric value, as a string doesn't make much sense.