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: Mon Jul 27 16:01:55 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
