Home » SQL & PL/SQL » SQL & PL/SQL » How to use computed result of one row in the next row for inserting in table (Oracle 11g)
How to use computed result of one row in the next row for inserting in table [message #639518] Thu, 09 July 2015 02:33
raman.virk
Messages: 4
Registered: June 2015
Junior Member
Hi All,

I have the below piece of code, in which I am selecting distinct values act_entry2case column from a staging table and then inserting data in main table by selecting rows wrt each of those distinct values from staging table. Staging table will have multiple rows
wrt each act_entry2case to uniquely identify each transaction.

Now I have 2 problems which I am not able to resolve:

1)First I need to use the value returned in one row in the next row for a particular CASE in the select clause. To make it clear in the below CASE, the TO_USER value will be returned when s_title='ASSIGN', now I need to use this value and insert in main table as from_user in the next row.
WHEN a.s_title = 'ASSIGN' THEN cxprepadm_cmds.F_get_user_login(
                 Trim(
                                                  Replace(
Substr(a.addnl_info,
Instr(Upper(a.addnl_info), 'TO', 1, 1) + 2,
                                                                              Instr(Upper(a.addnl_info), ',', 1, 1) -
                                    Instr(Upper(a.addnl_info), 'TO', 1, 1))
                            ,
                                                                      ',',
                            ''))) TO_USER  


2)While I am doing a select to fetch data in loop wrt each act_entry2case to insert in main table, I need to insert value in a column selecting it from a sequence, but I cant do it in the insert .. select statement
(INSERT INTO cxp_case_transfer_fact 
        SELECT '1',
               a.act_entry2case)

I have substituted '1' where I need the sequence value.

3)Thirdly I need to merge two rows against act_entry2case when s_title in 'DISPATCH' and ACCEPT. See the actual data and output below for clarity, the ACT_ENTRY2USER of DISPTACH will become the FROM_USER and the ACT_ENTRY2USER of ACCEPT will become the TO_USER.


The data in stage table is like below:
OBJID	      ACT_CODE	ENTRY_TIME	ADDNL_INFO	                      ACT_ENTRY2CASE ACT_ENTRY2USER  S_TITLE	
1342298332	900	13-MAY-15	Dispatched 110 from WIP.	            1342177317	1342177826   DISPATCH
1352309098	900	14-MAY-15	ACCEPT ENTRY FOR TEST	  	            1342177317	1342177787   ACCEPT
1342310075	10500	29-MAY-15	110 to hk804925, WIP default.               1342177317	1342177784   ASSIGN
1342310074	4100	29-MAY-15	110 yanked by rn406225 into WIPbin default. 1342177317	1342177826   YANKED


Output should be as below:
SEQUENCE_KEY ACT_ENTRY2CASE ENTRY_TIME	                FROM_USER  TO_USER     S_TITLE

1	     1342177317	    13-may-2015 04:32:32	1342177826 1342177787  DISPATCH/ACCEPT
2	     1342177317	    29-may-2015 08:28:33	1342177787 1342177738  ASSIGN
3	     1342177317	    29-may-2015 10:00:00	1342177738 1342177826  YANKED



create or replace  FUNCTION F_GET_USER_LOGIN(
      p_login_name IN VARCHAR)
    RETURN NUMBER
  IS
    l_user_key NUMBER := 0;
  BEGIN
    SELECT user_key
    INTO l_user_key
    FROM cxp_user_dim_1
       WHERE login_name = p_login_name;
    RETURN l_user_key;
  EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
  END F_GET_USER_LOGIN;




create or replace   FUNCTION F_GET_USER_KEY(
      p_user_objid IN VARCHAR)
    RETURN NUMBER
  IS
    l_user_key NUMBER := 0;
  BEGIN
    SELECT user_key
    INTO l_user_key
    FROM cxp_user_dim_1
    WHERE user_objid = p_user_objid;
    RETURN l_user_key;
  EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
  END F_GET_USER_KEY;
  

CREATE OR replace PROCEDURE Cxp_case_transfer
AS
  TYPE cxp_case_t
    IS TABLE OF cxp_case_transfer_stg.act_entry2case%TYPE;
  l_cxp_case_transfer CXP_CASE_T;
BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE cxp_case_transfer_fact';

    EXECUTE IMMEDIATE 'DROP SEQUENCE cxp_case_transfer_fact_seq';

    EXECUTE IMMEDIATE
    'CREATE SEQUENCE cxp_case_transfer_fact_seq increment by 1 start with 1';

    dbms_output.Put_line('here 1 after truncate');

    SELECT DISTINCT act_entry2case
    bulk   collect INTO l_cxp_case_transfer
    FROM   cxp_case_transfer_stg;

    dbms_output.Put_line ('count of distinct :'
                          ||l_cxp_case_transfer.count);

    FOR i IN l_cxp_case_transfer.first..l_cxp_case_transfer.last LOOP
        INSERT INTO cxp_case_transfer_fact
        SELECT '1',
               a.act_entry2case,
               a.entry_time,
               CASE
                 WHEN a.s_title IN ( 'ASSIGN', 'RETURN' ) THEN
                 F_get_user_key(a.act_entry2user)
                 ELSE F_get_user_key(Lag(a.act_entry2user, 1, a.act_entry2user)
                                       over (
                                         ORDER BY a.act_entry2case))
               END from_user,
               CASE
                 WHEN a.s_title = 'DISPATCH' THEN
                 F_get_user_key(Lead(a.act_entry2user, 1, a.act_entry2user)
                                  over (
                                    ORDER BY a.act_entry2case))
                 WHEN a.s_title = 'RETURN' THEN
                 F_get_user_key(Lag(a.act_entry2user,
                                1,
                                0)
                 over (
                   ORDER BY a.act_entry2case))
                 WHEN a.s_title = 'ASSIGN' THEN cxprepadm_cmds.F_get_user_login(
                 Trim(
                                                  Replace(
Substr(a.addnl_info,
Instr(Upper(a.addnl_info), 'TO', 1, 1) + 2,
                                                                              Instr(Upper(a.addnl_info), ',', 1, 1) -
                                    Instr(Upper(a.addnl_info), 'TO', 1, 1))
                            ,
                                                                      ',',
                            '')))
  ELSE F_get_user_key(a.act_entry2user)
END TOUSER,
CASE
  WHEN a.s_title = 'DISPATCH'
       AND Lead(a.s_title, 1, 0)
             over (
               ORDER BY a.act_entry2case) = 'ACCEPT' THEN a.s_title
                                                          ||'/'
                                                          || 
  Lead(a.s_title, 1, 0)
    over (
      ORDER BY a.act_entry2case)
  ELSE a.s_title
END
FROM   cxprepadm_cmds.cxp_case_transfer_stg a
WHERE  a.act_entry2case = L_cxp_case_transfer(i)
ORDER  BY a.act_entry2case,
          entry_time;
END LOOP;

FOR i IN (SELECT a.ROWID
          FROM   cxp_case_transfer_fact a) LOOP
    UPDATE cxp_case_transfer_fact
    SET    case_transfer_key = cxp_case_transfer_fact_seq.NEXTVAL
    WHERE  ROWID = i.ROWID;
END LOOP;

COMMIT;

dbms_output.Put_line (' after insert count : ');

COMMIT;
END cxp_case_transfer;  




CM: fixed code tag and added some more for readability

[Updated on: Thu, 09 July 2015 02:44] by Moderator

Report message to a moderator

Previous Topic: How to update substring using sql
Next Topic: Partition table - High_Value in DD-MON-YYYY format
Goto Forum:
  


Current Time: Fri Mar 29 07:41:02 CDT 2024