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
|
|
|
Goto Forum:
Current Time: Fri Mar 29 07:41:02 CDT 2024
|