Hi All,
i have a query like below, is it possible to Travers the main table recursively instead of multiple times like i did below.
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
WITH puzzle AS
(SELECT '1' as val,1 as row#,1 as col# from dual union all
SELECT 'A',1,2 from dual union all
SELECT 'B',1,3 from dual union all
SELECT 'C',1,4 from dual union all
SELECT '3',1,5 from dual union all
SELECT 'D',2,1 from dual union all
SELECT '2',2,2 from dual union all
SELECT 'E',2,3 from dual union all
SELECT '4',2,4 from dual union all
SELECT 'F',2,5 from dual union all
SELECT 'G',3,1 from dual union all
SELECT 'H',3,2 from dual union all
SELECT 'I',3,3 from dual union all
SELECT 'J',3,4 from dual union all
SELECT 'K',3,5 from dual union all
SELECT 'L',4,2 from dual union all
SELECT 'M',4,3 from dual union all
SELECT 'N',4,4 from dual),
interresult AS (SELECT val ,
nval ,
isnum
FROM
(SELECT d1.val ,
d2.val nval ,
CASE
WHEN d2.val IN ( '1','2','3','4' )
THEN 1
ELSE 0
END isnum
FROM puzzle d1
INNER JOIN puzzle d2
ON d1.row# = d2.row# - 2
AND d2.col# = d1.col# - 1 --SW
union
SELECT d1.val ,
d2.val nval ,
CASE
WHEN d2.val IN ( '1','2','3','4' )
THEN 1
ELSE 0
END isnum
FROM puzzle d1
INNER JOIN puzzle d2
ON d1.row# = d2.row# - 2
AND d2.col# = d1.col# + 1 --SE
union
SELECT d1.val ,
d2.val nval ,
CASE
WHEN d2.val IN ( '1','2','3','4' )
THEN 1
ELSE 0
END isnum
FROM puzzle d1
INNER JOIN puzzle d2
ON d1.row# = d2.row# + 2
AND d2.col# = d1.col# - 1 --NW
union
SELECT d1.val ,
d2.val nval ,
CASE
WHEN d2.val IN ( '1','2','3','4' )
THEN 1
ELSE 0
END isnum
FROM puzzle d1
INNER JOIN puzzle d2
ON d1.row# = d2.row# + 2
AND d2.col# = d1.col# + 1 --NE
union
SELECT d1.val ,
d2.val nval ,
CASE
WHEN d2.val IN ( '1','2','3','4' )
THEN 1
ELSE 0
END isnum
FROM puzzle d1
INNER JOIN puzzle d2
ON d2.col# = d1.col#+ 2
AND d2.row# = d1.row# + 1 --ES
union
SELECT d1.val ,
d2.val nval ,
CASE
WHEN d2.val IN ( '1','2','3','4' )
THEN 1
ELSE 0
END isnum
FROM puzzle d1
INNER JOIN puzzle d2
ON d2.col# = d1.col#+ 2
AND d2.row# = d1.row# - 1 --EN
union
SELECT d1.val ,
d2.val nval ,
CASE
WHEN d2.val IN ( '1','2','3','4' )
THEN 1
ELSE 0
END isnum
FROM puzzle d1
INNER JOIN puzzle d2
ON d2.col# = d1.col# - 2
AND d2.row# = d1.row# - 1 --WN
union
SELECT d1.val ,
d2.val nval ,
CASE
WHEN d2.val IN ( '1','2','3','4' )
THEN 1
ELSE 0
END isnum
FROM puzzle d1
INNER JOIN puzzle d2
ON d2.col# = d1.col# - 2
AND d2.row# = d1.row# + 1 --WS
minus
(
select 'D','L',0 from dual
union
select 'F','N',0 from dual
union
select 'M','G',0 from dual
union
select 'M','K',0 from dual)
) a
)
select count(*) from puzzle n0,interresult n1,interresult n2,interresult n3,interresult n4 ----,interresult n5
WHERE n0.val = n1.val
AND n1.nval = n2.val
AND n2.nval = n3.val
AND n3.nval = n4.val
--AND n4.nval = n5.val
AND CASE
WHEN n0.val IN (
'1'
,'2'
,'3'
,'4'
)
THEN 1
ELSE 0
END + n1.isnum + n2.isnum + n3.isnum + n4.isnum --+ n5.isnum
<=2;
Message was edited by: Midhun GT
Thank you all for your reply. I tried to rewrite the sql with WITH clause. It has test data and the corrected logic. Basically it is a puzzle that our group is trying to solve.
Developers are well equipped with other Languages and have done it easily. Being a DBA I'm trying to implement the logic in SQL.
The results are inline with others, but the performance is not great.
Hence wanted to check with this community to see if there is any scope for improvement. Any tips for improving this SQL would be helpful