Search code examples
sqloracle-databaseplsql

Inserting duplicate rows with negative amount from a previous record in Oracle PLSQL

I have a requirement where we need to create same set of rows that is equal to number of rows already present in the table and then update the amount. Basically, we are looking to create two sets of identical rows in the able where only difference is positive and negative amounts, similar to the table below. I need to generate negative amount rows for every positive amount row that is already existing in the table.

This is how end product should look like:

enter image description here

How can I achieve this? Currently the table only has positive rows. In the example image above, I tried validating based on odd and even line IDs but that would also mean first updating pre-existing positive amount rows with even numbers and then increment line ids to only odd numbers for negative amounts. But that did not work for me either.

Please advise the best way to achieve this. Here is the sample data

Note that table has only positive amount rows, we need to add negative amount rows. For each positive amount row there must be a negative amount row created for the same amount

CREATE TABLE XXRR_LINE_STG 
   (    DOCUMENT_ID NUMBER, 
    LINE_ID NUMBER, 
    LINE_VALUE NUMBER
   ) ;

REM INSERTING into PROCORE_WH_DATA.XXRR_LINE_STG
SET DEFINE OFF;
Insert into XXRR_LINE_STG (DOCUMENT_ID,LINE_ID,LINE_VALUE) values (543210,1,211.75);
Insert into XXRR_LINE_STG (DOCUMENT_ID,LINE_ID,LINE_VALUE) values (543210,2,311.75);
Insert into XXRR_LINE_STG (DOCUMENT_ID,LINE_ID,LINE_VALUE) values (543210,3,411.75);

Please help!!

Thank you Darsh

Solution

  • You could do something as simple as UNION ALL to add in the new rows and then refactor the line id:

    SELECT document_id,
       ROW_NUMBER() OVER (PARTITION BY document_id ORDER BY line_id) line_id,
       line_value
  FROM (SELECT document_id,
               line_id,
               line_value
          FROM xxrr_line_stg
        UNION ALL
        SELECT document_id,
               lineid+0.5 line_id,
               -line_value
          FROM xxrr_line_stg
         WHERE line_value > 0)

    Or a bit more compact (and a bit harder to understand), create the extra lines with a join to dummy rows:

    SELECT document_id,
       ROW_NUMBER() OVER (PARTITION BY document_id ORDER BY line_id,seq) line_id,
       DECODE(seq,1,line_value,-line_value) line_value
  FROM xxrr_line_stg,
       (SELECT LEVEL seq FROM dual CONNECT BY LEVEL <= 2)
 WHERE seq <= CASE WHEN (stg.line_value > 0) THEN 2 ELSE 1 END