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.

Holiday Challenge Time :)

CentinulDec 29 2009 — edited Nov 2 2010

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

This post has been answered by Rob van Wijk on Dec 29 2009

Jump to Answer

Rob van Wijk

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.

Marked as Answer by Centinul · Sep 27 2020

Centinul

Excellent solution Rob! :)

It definitely seemed like a problem where hierarchical queries would apply.

Frank Kulash

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.

1 person found this helpful

Hoek

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

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

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

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

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]

1 person found this helpful

Aketi Jyuuzou

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

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

1 - 10

Locked Post

New comments cannot be posted to this locked post.

Locked on Feb 2 2010

Added on Dec 29 2009

10 comments

4,050 views

SQL & PL/SQL

Holiday Challenge Time :)

Comments

Post Details