How to make existing values to null from the existing plsql logic-CodePudding

CREATE TABLE main_tab (
    seq_id      NUMBER(10),
    e_id        NUMBER(10),
    code        NUMBER(10),
    CONSTRAINT pk_main_tab PRIMARY KEY(seq_id)
);

INSERT INTO main_tab VALUES(1,11,3);

CREATE TABLE transact_tab (
    seq_id,
    e_id,
    code        NUMBER(10),
    start_date  DATE,
    end_date    DATE
);

I have two tables main_tab and transact_tab wherein I need to insert the records into the transact_tab table based on the column code of table main_tab. Ex. If code = 3 then in the table transact_tab it should load 3 records like 1, 2, and 3 with the start_date and end_date column as SYSDATE just for the current code value end_date would be null.

Code for the above requirement: -

DECLARE 
  l_transact_row transact_tab%ROWTYPE;
BEGIN
  FOR r IN (SELECT * FROM main_tab) LOOP
    FOR i IN 1 .. r.code LOOP
      BEGIN
        SELECT * INTO l_transact_row 
          FROM transact_tab
         WHERE e_id = r.e_id AND code = i; 
        IF l_transact_row.end_date IS NULL AND i <> r.code THEN
          UPDATE transact_tab SET end_date = SYSDATE WHERE e_id = r.e_id AND code = i;
        END IF;
      EXCEPTION WHEN NO_DATA_FOUND THEN
        INSERT INTO transact_tab (e_id, code,start_date, end_date)
          VALUES (r.e_id, i, SYSDATE, CASE WHEN i = r.code THEN NULL ELSE SYSDATE END);  
      END ;
    END LOOP;
  END LOOP;
END;

Database Version: Oracle 18c.

This code is perfectly working fine for top down approach wherein I need to load the data into the transact_tab but if my code gets decreased then the logic should handle this scenario as well. Example..if code gets changed from 3 to 2 then in this case it should update the already existing values of transact_tab to NULL if code is decreased.

Expected output of transact_tab if the code is decreased from 3 to 2 in main_tab should be:

 ------ ------ ------------ ---------- 
| e_id | code | start_date | end_date |
 ------ ------ ------------ ---------- 
|   11 |    1 | 19-06-22   | 19-06-22 |
|   11 |    2 | 19-06-22   | NULL     |
|   11 |    3 | NULL       | NULL     |
 ------ ------ ------------ ----------

CodePudding user response：

Cant you use something like following -

insert into transact_tab (e_id,code, start_date,end_date) 
select e_id,level,
case when code<level then null else sysdate end,
case when code<=level then null else sysdate end
from main_tab
connect by level<=3;

Refer fiddle here.

DECLARE 
  l_transact_row transact_tab%ROWTYPE;
BEGIN
  FOR r IN (SELECT * FROM main_tab) LOOP
    FOR i IN 1 .. r.code LOOP
      BEGIN
        SELECT * INTO l_transact_row 
          FROM transact_tab
         WHERE e_id = r.e_id AND code = i; 
          UPDATE transact_tab SET (start_date,end_date) = (select sysdate,sysdate from dual) WHERE code < r.code;
          UPDATE transact_tab SET (start_date,end_date) = (select null,null from dual) WHERE code > r.code;
          UPDATE transact_tab SET (start_date,end_date) = (select sysdate,null from dual) WHERE code = r.code;
      EXCEPTION WHEN NO_DATA_FOUND THEN
        INSERT INTO transact_tab (e_id, code,start_date, end_date)
          VALUES (r.e_id, i, SYSDATE, CASE WHEN i = r.code THEN NULL ELSE SYSDATE END);  
      END ;
    END LOOP;
  END LOOP;
END;

Refer to fiddle here.

Using the merge approach, it handles both increase and decrease in code values for main_tab.

merge into transact_tab t1
using 
(select * from 
(select e_id,level as lvl,
case when code<level then null else sysdate end start_date,
case when code<=level then null else sysdate end end_date
from main_tab
connect by level <= 
(select max(code) from (
(select max(code) code from transact_tab) union all
(select code from main_tab) )
)
 ) ) t2
  on (t1.e_id = t2.e_id
 and t1.code = t2.lvl)
 when matched then
 update set t1.start_date=t2.start_date, t1.end_date=t2.end_date
  when NOT matched then
 insert (e_id,code,start_date,end_date)
 VALUES
 (t2.e_id,t2.lvl,t2.start_date,t2.end_date);

Refer to the fiddle here, to see the merge approach in action.

CodePudding user response：

Edited version with less code. Basicaly - it works, but I didn't test all possible waays of changing the value of CODE on different days. Probably there is some more tuning to be done. Try it and check it.

Declare
    maxRepeats      Number(3) := 0;
    subMaxCode      Number(3) := 0;
Begin
    FOR r IN(SELECT * FROM a_main_tab) LOOP
        maxRepeats := r.CODE;
        SELECT Nvl(Max(CODE), 0) Into subMaxCode FROM a_transact_tab WHERE SEQ_ID = r.SEQ_ID And E_ID = r.E_ID; -- And start_date Is Not Null;
        FOR i IN 1..maxRepeats LOOP 
            If subMaxCode >= 0 And subMaxCode = maxRepeats THEN         
                EXIT;
            ElsIf subMaxCode >= 0 And subMaxCode < maxRepeats And i > subMaxCode THEN       
                        Begin
                            INSERT INTO a_transact_tab (seq_id, e_id, code, start_date, end_date)
                            VALUES(r.seq_id, r.e_id, i, SYSDATE, CASE WHEN i = maxRepeats THEN Null ELSE SYSDATE END);
                            commit;
                        End;
            ElsIf subMaxCode >= 0 And subMaxCode < maxRepeats And i <= subMaxCode THEN
                        Begin
                            UPDATE A_TRANSACT_TAB
                            SET start_date = CASE WHEN start_date Is Null Then SYSDATE ELSE start_date END,
                                end_date = CASE WHEN end_date Is Null Then SYSDATE ELSE end_date END
                            WHERE seq_id = r.seq_id and e_id = r.e_id and code = i;
                            commit;
                        End;
            ElsIf subMaxCode >= 0 And subMaxCode >= maxRepeats And i <= subMaxCode THEN             --UPD_x_TO_y
                Begin
                    UPDATE A_TRANSACT_TAB updt
                    SET updt.start_date = CASE WHEN CODE > maxRepeats THEN Null ELSE SYSDATE END,
                        updt.end_date = CASE WHEN CODE >= maxRepeats THEN Null ELSE SYSDATE END
                    WHERE updt.seq_id = r.seq_id and updt.e_id = r.e_id and updt.code Between maxRepeats and subMaxCode;
                    commit;
                End;
                EXIT;
            End If;
        END LOOP;
    END LOOP;
End;