Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

Creating Dr and Cr Lines - Balancing Lines || Splitting Rows || SQL

User_WDG4LFeb 2 2023 — edited Feb 2 2023

I have a table: xxtb_je_txn

CREATE TABLE XXTB_JE_TXN 
( "ENTITY" VARCHAR2(10 BYTE), 
"HEADER_ID" VARCHAR2(1000 BYTE), 
"NET_CR" NUMBER, 
"NET_DR" NUMBER
);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('401','10101',0,30);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('302','10101',0,20);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('402','10101',0,50);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('301','10101',100,0);

I want to split the row where NET_CR is 0 into three rows to create the Balance entries like:

ENTITY | HEADER_ID | NET_CR | NET_DR
401    | 10101     | 0      | 30
302    | 10101     | 0      | 20
402    | 10101     | 0      | 50
301    | 10101     | 30     | 0
301    | 10101     | 20     | 0
301    | 10101     | 50     | 0

How can I achieve this?

Comments
Post Details
Added on Feb 2 2023
19 comments
257 views