Hi,
I am trying to cut the text from row2 based on the format on row1 from table A,
It works fine for a single row inserted into A,
but when i added the second row it does not work as expected.
Can you let me know where we need to alter the sql statement.
CREATE TABLE A( A_ID VARCHAR2(50), A_TEXT VARCHAR2(200));
INSERT INTO A(A_ID,A_TEXT) VALUES('13233424','ABCDEFGHIJKLMNOPQRSTUV');
INSERT INTO A(A_ID,A_TEXT) VALUES('13233424','WXYZABCDEFGHIJKLMNOPQR');
WITH split_a_id AS
( SELECT a_text,
SUM(to_number(SUBSTR(a_id || '0',-level,1))) over (order by ROWNUM) AS cut_length
FROM a
CONNECT BY level<=LENGTH(a_id)
)
SELECT spl.cut_text
FROM
(SELECT SUBSTR(a_text,1,LENGTH(a_text) - cut_length) cut_text
FROM split_a_id
) spl
OUTPUT :
------------------------------------
CUT_TEXT
------------------------------
ABCDEFGHIJKLMNOPQRSTUV
ABCDEFGHIJKLMNOPQR
ABCDEFGHIJKLMNOP
ABCDEFGHIJKL
ABCDEFGHI
ABCDEF
ABCD
A
WXYZABCDEFGHIJKLMNOPQR
WXYZABCDEFGHIJKLMN
WXYZABCDEFGHIJKL
WXYZABCDEFGH
WXYZABCDE
WXYZAB
WXYZ
W
For Reference Initial Post at:
990152