Forum Stats

  • 3,733,249 Users
  • 2,246,737 Discussions
  • 7,856,634 Comments

Discussions

Holiday Challenge Time :)

Centinul
Centinul Member Posts: 6,871
edited November 2010 in SQL & PL/SQL
I have a daily puzzle calendar on my desk and I found a puzzle that I thought may be fun to solve using SQL.

Sample Data
WITH    matrix AS
(
        SELECT  'A' AS ID, 1 AS A,1 AS B,1 AS C,0 AS D,0 AS E,0 AS F,1 AS G,0 AS H,1 AS I,0 AS J FROM DUAL UNION ALL
        SELECT  'B' AS ID, 0,0,0,0,1,1,1,0,1,0 FROM DUAL UNION ALL
        SELECT  'C' AS ID, 0,1,1,1,1,0,0,0,0,1 FROM DUAL UNION ALL
        SELECT  'D' AS ID, 1,0,0,0,1,0,1,0,0,0 FROM DUAL UNION ALL
        SELECT  'E' AS ID, 1,1,0,0,0,0,0,0,0,1 FROM DUAL UNION ALL
        SELECT  'F' AS ID, 0,1,1,0,1,1,0,0,0,0 FROM DUAL UNION ALL
        SELECT  'G' AS ID, 0,1,0,1,1,1,0,1,0,1 FROM DUAL UNION ALL
        SELECT  'H' AS ID, 1,1,0,1,0,0,0,1,0,0 FROM DUAL UNION ALL
        SELECT  'I' AS ID, 0,1,1,1,0,1,1,1,0,1 FROM DUAL UNION ALL
        SELECT  'J' AS ID, 1,0,0,0,0,0,0,1,0,1 FROM DUAL UNION ALL
        SELECT  'K' AS ID, 1,0,0,1,1,1,0,1,1,0 FROM DUAL UNION ALL
        SELECT  'L' AS ID, 0,0,1,0,1,1,0,0,1,0 FROM DUAL UNION ALL
        SELECT  'M' AS ID, 0,0,1,0,0,0,0,1,1,1 FROM DUAL UNION ALL
        SELECT  'N' AS ID, 1,0,1,0,0,0,1,1,1,0 FROM DUAL UNION ALL
        SELECT  'O' AS ID, 1,0,0,1,1,0,1,0,0,0 FROM DUAL
)
SELECT  *
FROM    MATRIX
ORDER BY 1
Problem Statement

Suppose we have a matrix of one's and zero's like the following:
ID  A  B  C  D  E  F  G  H  I  J
-- -- -- -- -- -- -- -- -- -- --
A   1  1  1  0  0  0  1  0  1  0
B   0  0  0  0  1  1  1  0  1  0
C   0  1  1  1  1  0  0  0  0  1
D   1  0  0  0  1  0  1  0  0  0
E   1  1  0  0  0  0  0  0  0  1
F   0  1  1  0  1  1  0  0  0  0
G   0  1  0  1  1  1  0  1  0  1
H   1  1  0  1  0  0  0  1  0  0
I   0  1  1  1  0  1  1  1  0  1
J   1  0  0  0  0  0  0  1  0  1
K   1  0  0  1  1  1  0  1  1  0
L   0  0  1  0  1  1  0  0  1  0
M   0  0  1  0  0  0  0  1  1  1
N   1  0  1  0  0  0  1  1  1  0
O   1  0  0  1  1  0  1  0  0  0
Positions in this matrix are defined using (ROW,COLUMN) throughout the problem statement.

Challenge

Our goal is from a start position identified as (A,E), First Row, Fifth column, traverse DOWN the matrix to reach a valid point on row "O."

Restrictions

1. You can only move UP, DOWN, LEFT, or RIGHT (not diagonally) by one unit.
2. The path must be a repeating pattern of 0 1 0 1 0 1 ... etc For example a move from (A,E) to (B,E) is valid while a move from (A,E) to (A,F) is not.

Correct Solution

The correct solution has the following requirements:

1. Identifies the path from start to finish using an identifiable way to determine the ROW,COLUMN for each entry point in the path while abiding by the restrictions above.
2. PL/SQL and SQL are acceptable.
3. Solution must be perform well and be elegant as judged (loosely, since we don't have any voting functionality) by your peers. This solution will be marked as "Correct." Helpful points will be given to runner ups as determined by your peers.

Enjoy :D

Best Answer

  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Accepted Answer
    Nice challenge!

    Here is my solution:
    SQL> WITH matrix AS
      2  (
      3          SELECT  'A' AS ID, 1 AS A,1 AS B,1 AS C,0 AS D,0 AS E,0 AS F,1 AS G,0 AS H,1 AS I,0 AS J FROM DUAL UNION ALL
      4          SELECT  'B' AS ID, 0,0,0,0,1,1,1,0,1,0 FROM DUAL UNION ALL
      5          SELECT  'C' AS ID, 0,1,1,1,1,0,0,0,0,1 FROM DUAL UNION ALL
      6          SELECT  'D' AS ID, 1,0,0,0,1,0,1,0,0,0 FROM DUAL UNION ALL
      7          SELECT  'E' AS ID, 1,1,0,0,0,0,0,0,0,1 FROM DUAL UNION ALL
      8          SELECT  'F' AS ID, 0,1,1,0,1,1,0,0,0,0 FROM DUAL UNION ALL
      9          SELECT  'G' AS ID, 0,1,0,1,1,1,0,1,0,1 FROM DUAL UNION ALL
     10          SELECT  'H' AS ID, 1,1,0,1,0,0,0,1,0,0 FROM DUAL UNION ALL
     11          SELECT  'I' AS ID, 0,1,1,1,0,1,1,1,0,1 FROM DUAL UNION ALL
     12          SELECT  'J' AS ID, 1,0,0,0,0,0,0,1,0,1 FROM DUAL UNION ALL
     13          SELECT  'K' AS ID, 1,0,0,1,1,1,0,1,1,0 FROM DUAL UNION ALL
     14          SELECT  'L' AS ID, 0,0,1,0,1,1,0,0,1,0 FROM DUAL UNION ALL
     15          SELECT  'M' AS ID, 0,0,1,0,0,0,0,1,1,1 FROM DUAL UNION ALL
     16          SELECT  'N' AS ID, 1,0,1,0,0,0,1,1,1,0 FROM DUAL UNION ALL
     17          SELECT  'O' AS ID, 1,0,0,1,1,0,1,0,0,0 FROM DUAL
     18  )
     19  , matrix_transformed as
     20  ( select ascii(id) - 64 rw
     21         , cl
     22         , case cl
     23           when 1 then a
     24           when 2 then b
     25           when 3 then c
     26           when 4 then d
     27           when 5 then e
     28           when 6 then f
     29           when 7 then g
     30           when 8 then h
     31           when 9 then i
     32           when 10 then j
     33           end as value
     34      from matrix
     35         , ( select level cl from dual connect by level <= 10 )
     36  )
     37  , all_paths as
     38  ( select rw
     39         , sys_connect_by_path( '[' || chr(64+rw) || ',' || chr(64+cl) || ']', ';') scbp
     40      from matrix_transformed
     41     start with rw = 1
     42           and  cl = 5
     43   connect by nocycle value != prior value
     44           and (  ( rw = prior rw and cl = prior cl - 1 and prior rw != 15 )
     45               or ( rw = prior rw and cl = prior cl + 1 and prior rw != 15 )
     46               or ( rw = prior rw - 1 and cl = prior cl and prior rw != 15 )
     47               or ( rw = prior rw + 1 and cl = prior cl and prior rw != 15 )
     48               )
     49  )
     50  select ltrim(scbp,';') path
     51    from all_paths
     52   where rw = 15
     53  /
    
    PATH
    ------------------------------------------------------------------------------
    [A,E];[B,E];[B,D];[C,D];[D,D];[D,E];[D,F];[D,G];[C,G];[B,G];[B,H];[B,I];[C,I];
    [C,J];[D,J];[E,J];[F,J];[G,J];[G,I];[G,H];[G,G];[G,F];[H,F];[I,F];[J,F];[K,F];
    [K,G];[K,H];[L,H];[L,I];[L,J];[M,J];[N,J];[N,I];[O,I]
    
    [A,E];[B,E];[B,D];[C,D];[D,D];[D,E];[D,F];[D,G];[C,G];[B,G];[B,H];[B,I];[B,J];
    [C,J];[D,J];[E,J];[F,J];[G,J];[G,I];[G,H];[G,G];[G,F];[H,F];[I,F];[J,F];[K,F];
    [K,G];[K,H];[L,H];[L,I];[L,J];[M,J];[N,J];[N,I];[O,I]
    
    
    2 rows selected.
    Regards,
    Rob.

Answers

  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Accepted Answer
    Nice challenge!

    Here is my solution:
    SQL> WITH matrix AS
      2  (
      3          SELECT  'A' AS ID, 1 AS A,1 AS B,1 AS C,0 AS D,0 AS E,0 AS F,1 AS G,0 AS H,1 AS I,0 AS J FROM DUAL UNION ALL
      4          SELECT  'B' AS ID, 0,0,0,0,1,1,1,0,1,0 FROM DUAL UNION ALL
      5          SELECT  'C' AS ID, 0,1,1,1,1,0,0,0,0,1 FROM DUAL UNION ALL
      6          SELECT  'D' AS ID, 1,0,0,0,1,0,1,0,0,0 FROM DUAL UNION ALL
      7          SELECT  'E' AS ID, 1,1,0,0,0,0,0,0,0,1 FROM DUAL UNION ALL
      8          SELECT  'F' AS ID, 0,1,1,0,1,1,0,0,0,0 FROM DUAL UNION ALL
      9          SELECT  'G' AS ID, 0,1,0,1,1,1,0,1,0,1 FROM DUAL UNION ALL
     10          SELECT  'H' AS ID, 1,1,0,1,0,0,0,1,0,0 FROM DUAL UNION ALL
     11          SELECT  'I' AS ID, 0,1,1,1,0,1,1,1,0,1 FROM DUAL UNION ALL
     12          SELECT  'J' AS ID, 1,0,0,0,0,0,0,1,0,1 FROM DUAL UNION ALL
     13          SELECT  'K' AS ID, 1,0,0,1,1,1,0,1,1,0 FROM DUAL UNION ALL
     14          SELECT  'L' AS ID, 0,0,1,0,1,1,0,0,1,0 FROM DUAL UNION ALL
     15          SELECT  'M' AS ID, 0,0,1,0,0,0,0,1,1,1 FROM DUAL UNION ALL
     16          SELECT  'N' AS ID, 1,0,1,0,0,0,1,1,1,0 FROM DUAL UNION ALL
     17          SELECT  'O' AS ID, 1,0,0,1,1,0,1,0,0,0 FROM DUAL
     18  )
     19  , matrix_transformed as
     20  ( select ascii(id) - 64 rw
     21         , cl
     22         , case cl
     23           when 1 then a
     24           when 2 then b
     25           when 3 then c
     26           when 4 then d
     27           when 5 then e
     28           when 6 then f
     29           when 7 then g
     30           when 8 then h
     31           when 9 then i
     32           when 10 then j
     33           end as value
     34      from matrix
     35         , ( select level cl from dual connect by level <= 10 )
     36  )
     37  , all_paths as
     38  ( select rw
     39         , sys_connect_by_path( '[' || chr(64+rw) || ',' || chr(64+cl) || ']', ';') scbp
     40      from matrix_transformed
     41     start with rw = 1
     42           and  cl = 5
     43   connect by nocycle value != prior value
     44           and (  ( rw = prior rw and cl = prior cl - 1 and prior rw != 15 )
     45               or ( rw = prior rw and cl = prior cl + 1 and prior rw != 15 )
     46               or ( rw = prior rw - 1 and cl = prior cl and prior rw != 15 )
     47               or ( rw = prior rw + 1 and cl = prior cl and prior rw != 15 )
     48               )
     49  )
     50  select ltrim(scbp,';') path
     51    from all_paths
     52   where rw = 15
     53  /
    
    PATH
    ------------------------------------------------------------------------------
    [A,E];[B,E];[B,D];[C,D];[D,D];[D,E];[D,F];[D,G];[C,G];[B,G];[B,H];[B,I];[C,I];
    [C,J];[D,J];[E,J];[F,J];[G,J];[G,I];[G,H];[G,G];[G,F];[H,F];[I,F];[J,F];[K,F];
    [K,G];[K,H];[L,H];[L,I];[L,J];[M,J];[N,J];[N,I];[O,I]
    
    [A,E];[B,E];[B,D];[C,D];[D,D];[D,E];[D,F];[D,G];[C,G];[B,G];[B,H];[B,I];[B,J];
    [C,J];[D,J];[E,J];[F,J];[G,J];[G,I];[G,H];[G,G];[G,F];[H,F];[I,F];[J,F];[K,F];
    [K,G];[K,H];[L,H];[L,I];[L,J];[M,J];[N,J];[N,I];[O,I]
    
    
    2 rows selected.
    Regards,
    Rob.
  • Centinul
    Centinul Member Posts: 6,871
    Excellent solution Rob! :)

    It definitely seemed like a problem where hierarchical queries would apply.
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,492 Red Diamond
    edited December 2009
    Hi,

    CONNECT BY can do that:
    WITH	unpivoted	AS
    (
    	SELECT	r.id || c.id	AS coordinates
    	,	r.id		AS row_l
    	,	c.id		AS col_l
    	,	ASCII (r.id)	AS row_n
    	,	ASCII (c.id)	AS col_n
    	,	CASE	c.id
    			WHEN  'A'	THEN  r.a
    			WHEN  'B'	THEN  r.b
    			WHEN  'C'	THEN  r.c
    			WHEN  'D'	THEN  r.d
    			WHEN  'E'	THEN  r.e
    			WHEN  'F'	THEN  r.f
    			WHEN  'G'	THEN  r.g
    			WHEN  'H'	THEN  r.h
    			WHEN  'I'	THEN  r.i
    			WHEN  'J'	THEN  r.j
    		END	AS val
    	FROM		matrix	r
    	CROSS JOIN	matrix	c
    	WHERE		c.id	<= 'J'
    )
    SELECT	SYS_CONNECT_BY_PATH (coordinates, '>')	AS path
    FROM	unpivoted
    WHERE	coordinates	LIKE 'O_'
    START WITH	coordinates	= 'AE'
    CONNECT BY NOCYCLE  (	(	row_n				= PRIOR	row_n
    			AND	ABS (col_n - PRIOR col_n)	= 1
    			)
    		    OR  (	ABS (row_n - PRIOR row_n)	= 1
    			AND	col_n				= PRIOR col_n
    			)
    		    )
    	AND	    val	!= PRIOR val
    ;
    Edited by: Frank Kulash on Dec 29, 2009 10:04 AM
    Rob beat me to it, with essentially the same solution.
    Frank Kulash
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    still puzzling, but no holiday for me, so less time to find another alternative

    Centinul, this is a great puzzle/question/challenger.
    I thank you and Rob and Frank.
    Teaches me a lot.
  • Centinul
    Centinul Member Posts: 6,871
    Well this thread didn't generate as much interest as I thought, so I'm going to mark it answered. Since Rob and Frank's solutions are similar I'm going to give Rob the "Correct Answer" because he answered first, and Frank will receive some helpful points.

    Thanks to the both of you for posting, I always learn new and interesting ways to approach a problem from the both of you.

    Rob -- Thanks for the blog post as well, it's nice to see your thought process written out in words. :)
  • BluShadow
    BluShadow Member, Moderator Posts: 40,983 Red Diamond
    Centinul wrote:
    Well this thread didn't generate as much interest as I thought
    LOL! That's cos we were all on Holiday. :D
  • Centinul
    Centinul Member Posts: 6,871
    BluShadow wrote:
    Centinul wrote:
    Well this thread didn't generate as much interest as I thought
    LOL! That's cos we were all on Holiday. :D
    Good point :)
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited January 2010
    create table matrix AS
    SELECT 'A' AS ID, 1 AS A,1 AS B,1 AS C,0 AS D,0 AS E,0 AS F,1 AS G,0 AS H,1 AS I,0 AS J FROM DUAL UNION ALL
    SELECT 'B' AS ID, 0,0,0,0,1,1,1,0,1,0 FROM DUAL UNION ALL
    SELECT 'C' AS ID, 0,1,1,1,1,0,0,0,0,1 FROM DUAL UNION ALL
    SELECT 'D' AS ID, 1,0,0,0,1,0,1,0,0,0 FROM DUAL UNION ALL
    SELECT 'E' AS ID, 1,1,0,0,0,0,0,0,0,1 FROM DUAL UNION ALL
    SELECT 'F' AS ID, 0,1,1,0,1,1,0,0,0,0 FROM DUAL UNION ALL
    SELECT 'G' AS ID, 0,1,0,1,1,1,0,1,0,1 FROM DUAL UNION ALL
    SELECT 'H' AS ID, 1,1,0,1,0,0,0,1,0,0 FROM DUAL UNION ALL
    SELECT 'I' AS ID, 0,1,1,1,0,1,1,1,0,1 FROM DUAL UNION ALL
    SELECT 'J' AS ID, 1,0,0,0,0,0,0,1,0,1 FROM DUAL UNION ALL
    SELECT 'K' AS ID, 1,0,0,1,1,1,0,1,1,0 FROM DUAL UNION ALL
    SELECT 'L' AS ID, 0,0,1,0,1,1,0,0,1,0 FROM DUAL UNION ALL
    SELECT 'M' AS ID, 0,0,1,0,0,0,0,1,1,1 FROM DUAL UNION ALL
    SELECT 'N' AS ID, 1,0,1,0,0,0,1,1,1,0 FROM DUAL UNION ALL
    SELECT 'O' AS ID, 1,0,0,1,1,0,1,0,0,0 FROM DUAL;
    select substr(sys_connect_by_path('[' || ID || ',' || X || ']',';'),2) as path
      from matrix unPivot(vals for X in(A,B,C,D,E,F,G,H,I,J))
     where connect_by_IsLeaf = 1
       and ID = 'O'
    start with ID = 'A' and X='E'
    connect by nocycle (prior ID,prior X) in((chr(ascii(ID)+1),X),
                                             (chr(ascii(ID)-1),X),
                                             (ID,chr(ascii(X)+1)),
                                             (ID,chr(ascii(X)-1)))
           and mod(Level+1,2) = Vals
           and prior ID != 'O'; --If node is 'O', Search WILL stop!!!
    
    PATH
    --------------------------------------------------------------------------------
    [A,E];[B,E];[B,D];[C,D];[D,D];[D,E];[D,F];[D,G];[C,G];[B,G];[B,H];[B,I];[B,J];
    [C,J];[D,J];[E,J];[F,J];[G,J];[G,I];[G,H];[G,G];[G,F];[H,F];[I,F];[J,F];[K,F];
    [K,G];[K,H];[L,H];[L,I];[L,J];[M,J];[N,J];[N,I];[O,I]
    
    [A,E];[B,E];[B,D];[C,D];[D,D];[D,E];[D,F];[D,G];[C,G];[B,G];[B,H];[B,I];[C,I];
    [C,J];[D,J];[E,J];[F,J];[G,J];[G,I];[G,H];[G,G];[G,F];[H,F];[I,F];[J,F];[K,F];
    [K,G];[K,H];[L,H];[L,I];[L,J];[M,J];[N,J];[N,I];[O,I]
    Aketi Jyuuzou
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited November 2010
    A HAPPY NEW YEAR many guru and ace and anyone :-)

    Hehe I like unPivot and using MultiColumns In condition at connect by clause B-)

    I understood that UnPivot is one of from clause from below thread
    evaluated order of Pivot and UnPivot in select statement
    995121

    My Oracle Hierarchical Queries articles ;-)
    http://codezine.jp/article/corner/301

    I like Knight's_tour :D
    http://en.wikipedia.org/wiki/Knight's_tour
    And My homepage mentions it
    http://www.geocities.jp/oraclesqlpuzzle/plsql-3.html
  • BobLilly
    BobLilly Member Posts: 711 Bronze Trophy
    Building on Frank's solution, the following generates maps showing the valid paths. If I had 11g handy I could use pivot and unpivot to great advantage, but someone else will have to make that attempt.

    Interesting to note that if you generate a random matrix it most often produces no solutions at all, but when it does it usually produces a large number of valid paths.

    Regards,
    Bob

    Random matrix:
    create global temporary table  matrix on commit preserve rows AS
    select chr(64+level) as ID
    , ROUND(DBMS_RANDOM.VALUE(),0) as A 
    , ROUND(DBMS_RANDOM.VALUE(),0) as B 
    , ROUND(DBMS_RANDOM.VALUE(),0) as C 
    , ROUND(DBMS_RANDOM.VALUE(),0) as D 
    , ROUND(DBMS_RANDOM.VALUE(),0) as E 
    , ROUND(DBMS_RANDOM.VALUE(),0) as F 
    , ROUND(DBMS_RANDOM.VALUE(),0) as G 
    , ROUND(DBMS_RANDOM.VALUE(),0) as H 
    , ROUND(DBMS_RANDOM.VALUE(),0) as I 
    , ROUND(DBMS_RANDOM.VALUE(),0) as J 
    from dual 
    connect by level <= 15
    Map generator:
    select pnum, chr(64+rw) as id, a, b, c, d, e, f, g, h, i, j from (
    select * from (
    WITH   matrix AS
    (
            SELECT  'A' AS ID, 1 AS A,1 AS B,1 AS C,0 AS D,0 AS E,0 AS F,1 AS G,0 AS H,1 AS I,0 AS J FROM DUAL UNION ALL
            SELECT  'B' AS ID, 0,0,0,0,1,1,1,0,1,0 FROM DUAL UNION ALL
            SELECT  'C' AS ID, 0,1,1,1,1,0,0,0,0,1 FROM DUAL UNION ALL
            SELECT  'D' AS ID, 1,0,0,0,1,0,1,0,0,0 FROM DUAL UNION ALL
            SELECT  'E' AS ID, 1,1,0,0,0,0,0,0,0,1 FROM DUAL UNION ALL
            SELECT  'F' AS ID, 0,1,1,0,1,1,0,0,0,0 FROM DUAL UNION ALL
            SELECT  'G' AS ID, 0,1,0,1,1,1,0,1,0,1 FROM DUAL UNION ALL
            SELECT  'H' AS ID, 1,1,0,1,0,0,0,1,0,0 FROM DUAL UNION ALL
            SELECT  'I' AS ID, 0,1,1,1,0,1,1,1,0,1 FROM DUAL UNION ALL
            SELECT  'J' AS ID, 1,0,0,0,0,0,0,1,0,1 FROM DUAL UNION ALL
            SELECT  'K' AS ID, 1,0,0,1,1,1,0,1,1,0 FROM DUAL UNION ALL
            SELECT  'L' AS ID, 0,0,1,0,1,1,0,0,1,0 FROM DUAL UNION ALL
            SELECT  'M' AS ID, 0,0,1,0,0,0,0,1,1,1 FROM DUAL UNION ALL
            SELECT  'N' AS ID, 1,0,1,0,0,0,1,1,1,0 FROM DUAL UNION ALL
            SELECT  'O' AS ID, 1,0,0,1,1,0,1,0,0,0 FROM DUAL
    )
    , unpivoted	AS
    (
    	SELECT	r.id || c.id	AS coordinates
    	,	r.id		AS row_l
    	,	c.id		AS col_l
    	,	ASCII (r.id)	AS row_n
    	,	ASCII (c.id)	AS col_n
    	,	CASE	c.id
    			WHEN  'A'	THEN  r.a
    			WHEN  'B'	THEN  r.b
    			WHEN  'C'	THEN  r.c
    			WHEN  'D'	THEN  r.d
    			WHEN  'E'	THEN  r.e
    			WHEN  'F'	THEN  r.f
    			WHEN  'G'	THEN  r.g
    			WHEN  'H'	THEN  r.h
    			WHEN  'I'	THEN  r.i
    			WHEN  'J'	THEN  r.j
    		END	AS val
    	FROM		matrix	r
    	CROSS JOIN	matrix	c
    	WHERE		c.id	<= 'J'
    )
    SELECT	rownum as pnum , SYS_CONNECT_BY_PATH (coordinates, '>')	AS path
           , translate(sys_connect_by_path(case when prior row_n is null then coordinates 
                                      when row_n=15 then 'v'||coordinates
                                      when row_n>prior row_n then 'v' 
                                      when row_n<prior row_n then '^' 
                                      when col_n<prior col_n then '<' 
                                      else                  '>' 
                                      end,';'),'~;','~') as route
    FROM	unpivoted
    WHERE	coordinates	LIKE 'O_'
    START WITH	coordinates	= 'AE'
    CONNECT BY NOCYCLE  (	(	row_n				= PRIOR	row_n
    			AND	ABS (col_n - PRIOR col_n)	= 1
    			)
    		    OR  (	ABS (row_n - PRIOR row_n)	= 1
    			AND	col_n				= PRIOR col_n
    			)
    		    )
    	AND	    val	!= PRIOR val
      and prior row_l != 'O'
    )
    model
    partition by (pnum, path)
    dimension by (1 rw) measures (0 Z,'  ' X,'  ' Y,' ' L, ' ' A, ' ' B, ' ' C, ' ' D, ' ' E, ' ' F, ' ' G, ' ' H, ' ' I, ' ' J)
    rules upsert all sequential order 
    (
      Z[for rw from 1 to 15 increment 1]=instr(cv(path),chr(64+cv(rw))||'A')
    , X[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then substr(cv(path),Z[cv()],2) else '  ' end
    , Y[for rw from 1 to 15 increment 1]=case when cv(rw)<15 then substr(cv(path),Z[cv()]+3,2) else '  ' end
    , L[for rw from 1 to 15 increment 1]=case when cv(rw)=15 then '*'
                                              when substr(Y[cv()],1,1)< substr(X[cv()],1,1) then '^'
                                              when substr(Y[cv()],1,1)> substr(X[cv()],1,1) then 'v'
                                              when substr(Y[cv()],2,1)> substr(X[cv()],2,1) then '>'
                                              else  '<' end
    , A[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then L[cv()] else ' ' end
    , Z[for rw from 1 to 15 increment 1]=instr(cv(path),chr(64+cv(rw))||'B')
    , X[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then substr(cv(path),Z[cv()],2) else '  ' end
    , Y[for rw from 1 to 15 increment 1]=case when cv(rw)<15 then substr(cv(path),Z[cv()]+3,2) else '  ' end
    , L[for rw from 1 to 15 increment 1]=case when cv(rw)=15 then L[cv()]
                                              when substr(Y[cv()],1,1)< substr(X[cv()],1,1) then '^'
                                              when substr(Y[cv()],1,1)> substr(X[cv()],1,1) then 'v'
                                              when substr(Y[cv()],2,1)> substr(X[cv()],2,1) then '>'
                                              else  '<' end
    , B[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then L[cv()] else ' ' end
    , Z[for rw from 1 to 15 increment 1]=instr(cv(path),chr(64+cv(rw))||'C')
    , X[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then substr(cv(path),Z[cv()],2) else '  ' end
    , Y[for rw from 1 to 15 increment 1]=case when cv(rw)<15 then substr(cv(path),Z[cv()]+3,2) else '  ' end
    , L[for rw from 1 to 15 increment 1]=case when cv(rw)=15 then L[cv()]
                                              when substr(Y[cv()],1,1)< substr(X[cv()],1,1) then '^'
                                              when substr(Y[cv()],1,1)> substr(X[cv()],1,1) then 'v'
                                              when substr(Y[cv()],2,1)> substr(X[cv()],2,1) then '>'
                                              else  '<' end
    , C[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then L[cv()] else ' ' end
    , Z[for rw from 1 to 15 increment 1]=instr(cv(path),chr(64+cv(rw))||'D')
    , X[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then substr(cv(path),Z[cv()],2) else '  ' end
    , Y[for rw from 1 to 15 increment 1]=case when cv(rw)<15 then substr(cv(path),Z[cv()]+3,2) else '  ' end
    , L[for rw from 1 to 15 increment 1]=case when cv(rw)=15 then '*'
                                              when substr(Y[cv()],1,1)< substr(X[cv()],1,1) then '^'
                                              when substr(Y[cv()],1,1)> substr(X[cv()],1,1) then 'v'
                                              when substr(Y[cv()],2,1)> substr(X[cv()],2,1) then '>'
                                              else  '<' end
    , D[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then L[cv()] else ' ' end
    , Z[for rw from 1 to 15 increment 1]=instr(cv(path),chr(64+cv(rw))||'E')
    , X[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then substr(cv(path),Z[cv()],2) else '  ' end
    , Y[for rw from 1 to 15 increment 1]=case when cv(rw)<15 then substr(cv(path),Z[cv()]+3,2) else '  ' end
    , L[for rw from 1 to 15 increment 1]=case when cv(rw)=15 then '*'
                                              when substr(Y[cv()],1,1)< substr(X[cv()],1,1) then '^'
                                              when substr(Y[cv()],1,1)> substr(X[cv()],1,1) then 'v'
                                              when substr(Y[cv()],2,1)> substr(X[cv()],2,1) then '>'
                                              else  '<' end
    , E[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then L[cv()] else ' ' end
    , Z[for rw from 1 to 15 increment 1]=instr(cv(path),chr(64+cv(rw))||'F')
    , X[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then substr(cv(path),Z[cv()],2) else '  ' end
    , Y[for rw from 1 to 15 increment 1]=case when cv(rw)<15 then substr(cv(path),Z[cv()]+3,2) else '  ' end
    , L[for rw from 1 to 15 increment 1]=case when cv(rw)=15 then '*'
                                              when substr(Y[cv()],1,1)< substr(X[cv()],1,1) then '^'
                                              when substr(Y[cv()],1,1)> substr(X[cv()],1,1) then 'v'
                                              when substr(Y[cv()],2,1)> substr(X[cv()],2,1) then '>'
                                              else  '<' end
    , F[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then L[cv()] else ' ' end
    , Z[for rw from 1 to 15 increment 1]=instr(cv(path),chr(64+cv(rw))||'G')
    , X[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then substr(cv(path),Z[cv()],2) else '  ' end
    , Y[for rw from 1 to 15 increment 1]=case when cv(rw)<15 then substr(cv(path),Z[cv()]+3,2) else '  ' end
    , L[for rw from 1 to 15 increment 1]=case when cv(rw)=15 then '*'
                                              when substr(Y[cv()],1,1)< substr(X[cv()],1,1) then '^'
                                              when substr(Y[cv()],1,1)> substr(X[cv()],1,1) then 'v'
                                              when substr(Y[cv()],2,1)> substr(X[cv()],2,1) then '>'
                                              else  '<' end
    , G[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then L[cv()] else ' ' end
    , Z[for rw from 1 to 15 increment 1]=instr(cv(path),chr(64+cv(rw))||'H')
    , X[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then substr(cv(path),Z[cv()],2) else '  ' end
    , Y[for rw from 1 to 15 increment 1]=case when cv(rw)<15 then substr(cv(path),Z[cv()]+3,2) else '  ' end
    , L[for rw from 1 to 15 increment 1]=case when cv(rw)=15 then '*'
                                              when substr(Y[cv()],1,1)< substr(X[cv()],1,1) then '^'
                                              when substr(Y[cv()],1,1)> substr(X[cv()],1,1) then 'v'
                                              when substr(Y[cv()],2,1)> substr(X[cv()],2,1) then '>'
                                              else  '<' end
    , H[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then L[cv()] else ' ' end
    , Z[for rw from 1 to 15 increment 1]=instr(cv(path),chr(64+cv(rw))||'I')
    , X[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then substr(cv(path),Z[cv()],2) else '  ' end
    , Y[for rw from 1 to 15 increment 1]=case when cv(rw)<15 then substr(cv(path),Z[cv()]+3,2) else '  ' end
    , L[for rw from 1 to 15 increment 1]=case when cv(rw)=15 then '*'
                                              when substr(Y[cv()],1,1)< substr(X[cv()],1,1) then '^'
                                              when substr(Y[cv()],1,1)> substr(X[cv()],1,1) then 'v'
                                              when substr(Y[cv()],2,1)> substr(X[cv()],2,1) then '>'
                                              else  '<' end
    , I[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then L[cv()] else ' ' end
    , Z[for rw from 1 to 15 increment 1]=instr(cv(path),chr(64+cv(rw))||'J')
    , X[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then substr(cv(path),Z[cv()],2) else '  ' end
    , Y[for rw from 1 to 15 increment 1]=case when cv(rw)<15 then substr(cv(path),Z[cv()]+3,2) else '  ' end
    , L[for rw from 1 to 15 increment 1]=case when cv(rw)=15 then '*'
                                              when substr(Y[cv()],1,1)< substr(X[cv()],1,1) then '^'
                                              when substr(Y[cv()],1,1)> substr(X[cv()],1,1) then 'v'
                                              when substr(Y[cv()],2,1)> substr(X[cv()],2,1) then '>'
                                              else  '<' end
    , J[for rw from 1 to 15 increment 1]=case when Z[cv()]>0 then L[cv()] else ' ' end
    )
    )
    order by 1,2
This discussion has been closed.