Forum Stats

  • 3,839,383 Users
  • 2,262,486 Discussions
  • 7,900,956 Comments

Discussions

SQL work for single row and not multiple

Ludy
Ludy Member Posts: 50
edited Nov 18, 2009 6:30AM in SQL & PL/SQL
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

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,250 Red Diamond
    edited Nov 17, 2009 2:47PM 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 between
     tags 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

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,250 Red Diamond
    edited Nov 17, 2009 2:47PM 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 between
     tags 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.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • Ludy
    Ludy Member Posts: 50
    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 between
     tags, and sorry about the link.                                                                                                                                                                                                                                                                                                                                                                                                    
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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 :D
This discussion has been closed.