Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.2K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 436 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
SQL work for single row and not multiple

Ludy
Member Posts: 50
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
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
Best Answer
-
Hi,
When you say "CONNECT BY LEVEL <= x" , make sure at least one of the following is true:
(1) the table in the query has only one row, or
(2) you know what you're doing.
In other situations, create a Counter Table that has all the numbers you'll ever need, and join to the Counter Table where you otherwise would do a CONNECT BY.
In the definition for the Counter Table, the CONNECT BY clause will resemble your original CONNECT BY clause, but instead of "CONNECT BY LEVEL <= x" it will be "CONNECT LEVEL <= *(SELECT MAX (x) ...)* ".
When you use the counter table, the join condition will be similar to your original CONNECT BY condition.WITH cntr AS ( SELECT LEVEL AS n FROM dual CONNECT BY LEVEL <= ( SELECT MAX (LENGTH (a_id)) FROM a ) ) , split_a_id AS ( SELECT a.a_text, -- a.a_id, -- For debugging only -- c.n, -- For debugging only SUM (TO_NUMBER ( SUBSTR ( a.a_id || '0' , -c.n , 1 ) ) ) OVER ( PARTITION BY a.a_text ORDER BY c.n ) AS cut_length FROM a a JOIN cntr c ON c.n <= LENGTH (a.a_id) ) SELECT SUBSTR ( a_text , 1 , LENGTH (a_text) - cut_length ) AS cut_text FROM split_a_id ORDER BY a_text , cut_text DESC ;
Now that you're dealing with multiple rows, you need a PARTITION BY clause in the analytic SUM function.
Thanks for including the CREATE TABLE and INSERT statements.
Please format your code, and post it betweentags so this site won't remove any spaces. The link was a good idea, but it seems to be a link to the wrong thread. Edited by: Frank Kulash on Nov 17, 2009 2:46 PM Commented out 2 line I forgot to delete after testing.
Answers
-
Hi,
When you say "CONNECT BY LEVEL <= x" , make sure at least one of the following is true:
(1) the table in the query has only one row, or
(2) you know what you're doing.
In other situations, create a Counter Table that has all the numbers you'll ever need, and join to the Counter Table where you otherwise would do a CONNECT BY.
In the definition for the Counter Table, the CONNECT BY clause will resemble your original CONNECT BY clause, but instead of "CONNECT BY LEVEL <= x" it will be "CONNECT LEVEL <= *(SELECT MAX (x) ...)* ".
When you use the counter table, the join condition will be similar to your original CONNECT BY condition.WITH cntr AS ( SELECT LEVEL AS n FROM dual CONNECT BY LEVEL <= ( SELECT MAX (LENGTH (a_id)) FROM a ) ) , split_a_id AS ( SELECT a.a_text, -- a.a_id, -- For debugging only -- c.n, -- For debugging only SUM (TO_NUMBER ( SUBSTR ( a.a_id || '0' , -c.n , 1 ) ) ) OVER ( PARTITION BY a.a_text ORDER BY c.n ) AS cut_length FROM a a JOIN cntr c ON c.n <= LENGTH (a.a_id) ) SELECT SUBSTR ( a_text , 1 , LENGTH (a_text) - cut_length ) AS cut_text FROM split_a_id ORDER BY a_text , cut_text DESC ;
Now that you're dealing with multiple rows, you need a PARTITION BY clause in the analytic SUM function.
Thanks for including the CREATE TABLE and INSERT statements.
Please format your code, and post it betweentags so this site won't remove any spaces. The link was a good idea, but it seems to be a link to the wrong thread. Edited by: Frank Kulash on Nov 17, 2009 2:46 PM Commented out 2 line I forgot to delete after testing.
-
Thanks Frank Kulash,
now got better understanding in using the LEVEL in CONNECT BY.
Now things are getting clearer to me.
sure will post code betweentags, and sorry about the link.
-
I like recursive with clause B-)
I do Not have Oracle11gR2
Therefore I made below solution using PostgreSQL8.4 :8}CREATE TABLE TTT(ID text,Val text); INSERT INTO TTT(ID,Val) VALUES('13233424','ABCDEFGHIJKLMNOPQRSTUV'); INSERT INTO TTT(ID,Val) VALUES('13233424','WXYZABCDEFGHIJKLMNOPQR'); with recursive rec(ID,Val,LV,cuts) as( select ID,Val,1 ,substr(ID,1,1) from TTT union all select ID,Val,LV+1,substr(ID,LV+1,1) from rec where LV < length(ID)) select substr(Val,1,(sum(cuts::integer) over(partition by Val order by LV))::integer) as CUT_TEXT from rec order by Val,LV desc; cut_text ----------------------- ABCDEFGHIJKLMNOPQRSTUV ABCDEFGHIJKLMNOPQR ABCDEFGHIJKLMNOP ABCDEFGHIJKL ABCDEFGHI ABCDEF ABCD A WXYZABCDEFGHIJKLMNOPQR WXYZABCDEFGHIJKLMN WXYZABCDEFGHIJKL WXYZABCDEFGH WXYZABCDE WXYZAB WXYZ W
I like model clause solution too
This discussion has been closed.