Copy Code code as follows:
CREATE TABLE A_test
(
Payout_item_code VARCHAR2 () NOT NULL,
Formula_det VARCHAR2 (1000)
)
CREATE TABLE B_test
(
element_id VARCHAR2 (5) NOT NULL,
NAME VARCHAR2 (41)
)
Formula_det column element_id replaced with name
The test data are as follows
Copy Code code as follows:
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30228 ', ' ({30015}+{30016}) *450 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30102 ', ' ({30015}+{30016}) *1500 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30102 ', ' ({30015}+{30016}) *5000 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30102 ', ' ({30015}+{30016}) *2500 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30102 ', ' ({30015}+{30016}) *2300 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30102 ', ' ({30015}+{30016}) *1150 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30104 ', ' ({30015}+{30016}) *300*12 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30057}*2300 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30102 ', ' ({30015}+{30016}) *5000 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30102 ', ' ({30015}+{30016}) *3000 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30102 ', ' ({30015}+{30016}) *1500 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30006}+{30061}+{30008} ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30057}*3800*12 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30210 ', ' ({30030}+{30031}+{30032}) *38000+{30033}*23000 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30210 ', ' ({30030}+{30031}+{30032}+{30033}) *38000+{30036}*10000 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30229 ', ' ({30015}+{30016}) *1400 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30228 ', ' ({30015}+{30016}) *450 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30216 ', ' ({30015}+{30016}) *1300 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30216 ', ' ({30015}+{30016}) *650 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30307 ', ' ({30015}+{30016}) *360 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30302 ', ' {30051} ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30302 ', ' {30052} ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30302 ', ' {30053} ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30302 ', ' {30054} ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30302 ', ' {30055} ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30302 ', ' {30056} ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30057}*4000 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30057}*3800 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30057}*100*12 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30057}*500*12 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30060}*0 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30057}/{30057}*150000 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30057}*6000 ');
Copy Code code as follows:
Insert into B_test (element_id, NAME)
VALUES (' 30006 ', ' A1 ');
Insert into B_test (element_id, NAME)
VALUES (' 30008 ', ' A2 ');
Insert into B_test (element_id, NAME)
VALUES (' 30009 ', ' A3 ');
Insert into B_test (element_id, NAME)
VALUES (' 30010 ', ' A4 ');
Insert into B_test (element_id, NAME)
VALUES (' 30015 ', ' A5 ');
Insert into B_test (element_id, NAME)
VALUES (' 30016 ', ' A6 ');
Insert into B_test (element_id, NAME)
VALUES (' 30017 ', ' A7 ');
Insert into B_test (element_id, NAME)
VALUES (' 30018 ', ' A8 ');
Insert into B_test (element_id, NAME)
VALUES (' 30019 ', ' A9 ');
Insert into B_test (element_id, NAME)
VALUES (' 30020 ', ' A10 ');
Insert into B_test (element_id, NAME)
VALUES (' 30021 ', ' A11 ');
Insert into B_test (element_id, NAME)
VALUES (' 30022 ', ' A12 ');
Insert into B_test (element_id, NAME)
VALUES (' 30023 ', ' A13 ');
Insert into B_test (element_id, NAME)
VALUES (' 30024 ', ' A14 ');
Insert into B_test (element_id, NAME)
VALUES (' 30025 ', ' A15 ');
Insert into B_test (element_id, NAME)
VALUES (' 30026 ', ' A16 ');
Insert into B_test (element_id, NAME)
VALUES (' 30027 ', ' A17 ');
Insert into B_test (element_id, NAME)
VALUES (' 30028 ', ' A18 ');
Insert into B_test (element_id, NAME)
VALUES (' 30029 ', ' A19 ');
Insert into B_test (element_id, NAME)
VALUES (' 30030 ', ' A20 ');
Insert into B_test (element_id, NAME)
VALUES (' 30031 ', ' A21 ');
Insert into B_test (element_id, NAME)
VALUES (' 30032 ', ' A22 ');
Insert into B_test (element_id, NAME)
VALUES (' 30033 ', ' A23 ');
Insert into B_test (element_id, NAME)
VALUES (' 30034 ', ' A24 ');
Insert into B_test (element_id, NAME)
VALUES (' 30035 ', ' A25 ');
Insert into B_test (element_id, NAME)
VALUES (' 30036 ', ' A26 ');
Insert into B_test (element_id, NAME)
VALUES (' 30037 ', ' A27 ');
Insert into B_test (element_id, NAME)
VALUES (' 30038 ', ' A28 ');
Insert into B_test (element_id, NAME)
VALUES (' 30039 ', ' A29 ');
Insert into B_test (element_id, NAME)
VALUES (' 30040 ', ' A30 ');
Insert into B_test (element_id, NAME)
VALUES (' 30041 ', ' A31 ');
Insert into B_test (element_id, NAME)
VALUES (' 30042 ', ' A32 ');
Insert into B_test (element_id, NAME)
VALUES (' 30043 ', ' A33 ');
Insert into B_test (element_id, NAME)
VALUES (' 30044 ', ' A34 ');
Insert into B_test (element_id, NAME)
VALUES (' 30045 ', ' A35 ');
Insert into B_test (element_id, NAME)
VALUES (' 30046 ', ' A36 ');
Insert into B_test (element_id, NAME)
VALUES (' 30047 ', ' A37 ');
Insert into B_test (element_id, NAME)
VALUES (' 30048 ', ' A38 ');
Insert into B_test (element_id, NAME)
VALUES (' 30049 ', ' A39 ');
Insert into B_test (element_id, NAME)
VALUES (' 30050 ', ' A40 ');
Insert into B_test (element_id, NAME)
VALUES (' 30051 ', ' A41 ');
Insert into B_test (element_id, NAME)
VALUES (' 30052 ', ' a42 ');
Insert into B_test (element_id, NAME)
VALUES (' 30053 ', ' A43 ');
Insert into B_test (element_id, NAME)
VALUES (' 30054 ', ' A44 ');
Insert into B_test (element_id, NAME)
VALUES (' 30055 ', ' A45 ');
Insert into B_test (element_id, NAME)
VALUES (' 30056 ', ' A46 ');
Insert into B_test (element_id, NAME)
VALUES (' 30057 ', ' A47 ');
Insert into B_test (element_id, NAME)
VALUES (' 30058 ', ' A48 ');
Insert into B_test (element_id, NAME)
VALUES (' 30059 ', ' A49 ');
Insert into B_test (element_id, NAME)
VALUES (' 30060 ', ' A50 ');
Insert into B_test (element_id, NAME)
VALUES (' 30061 ', ' A51 ');
If you do this with a function or SP, there is no difficulty.
But it's more difficult to do it with SQL.
Copy Code code as follows:
Select GID, Payout_item_code, Formula_det, Max (substr (TXT, 1, Length (TXT)-1)) from (
Select A.gid,
A.payout_item_code,
A.formula_det,
Replace (Sys_connect_by_path decode (b.element_id, NULL, a.signal, replace (signal, b.element_id, b.name)), ' # # ', ' # # ', ") txt
From
(select GID, Payout_item_code, Formula_det, Row_number () over (partition by GID Order by level) RN,
SUBSTR (Formula_det, Decode (rownum-(allcnt-selfcnt), 1, 1, InStr (Formula_det, '} ', 1, rownum-(allcnt-selfcnt)-1) +1), InStr (Formula_det, '} ', 1, rownum-(allcnt-selfcnt))-decode (rownum-(allcnt-selfcnt), 1, 0, InStr (Formula_det, '} ', 1, rownum-(allcnt-selfcnt)-1)) signal
From (select A.payout_item_code, A.rowid GID,
a.formula_det| | '} ' Formula_det,
Length (A.formula_det)-
Length (replace (A.formula_det, '} ', ')) + 1 selfcnt,
Sum (Length (a.formula_det)-Length (replace (A.formula_det, '} ', ')) (+1) over (order by ROWID) allcnt, sum (Length ( A.formula_det)-Length (replace (A.formula_det, '} ', ')) (+1) over () sumcnt
From A_test a) T1
Start with (allcnt-selfcnt) =0 Connect by RowNum < sumcnt+1 and InStr (Formula_det, '} ', 1, rownum-(allcnt-selfcnt)) > 0) A
Left join B_test B on InStr (a.signal| | '} ', ' {' | | b.element_id| | '} ', 1, 1 >0
Start with A.RN = 1 Connect by prior A.gid = A.gid and prior a.rn + 1 = a.rn)
GROUP by GID, Payout_item_code, Formula_det
- 30 stand
- time: October 21, 2009 17:09:43
- Please respect the original works. Reprint please keep the article integrity, and in the form of hyperlinks annotated original author " inthirties (30) " and source " http://blog.csdn.net/inthirties/archive/2009/10/21/4706281.aspx Font size= "2", in-depth discussion can be contacted inthirties@gmail.com
