Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQL work for single row and not multiple

LudyNov 17 2009 — edited Nov 18 2009
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
This post has been answered by Frank Kulash on Nov 17 2009
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 16 2009
Added on Nov 17 2009
3 comments
1,105 views