OraQA

The following is an interesting puzzle posted by Jsoftware: Find a 9-digit number a[1]a[2]a[3]…. containing all the digits 1,2,…,9 such that “K” divides a[1]a[2]a[3]…..a[k] for each k = 1,2,3….9.

———————-SQL Solution—————

SQL> set timing on
SQL> set serveroutput on

SELECT TO_NUMBER( STR) NUM
FROM
(WITH NUM AS (SELECT LEVEL n FROM dual CONNECT BY LEVEL <= 9)
 SELECT STR
 FROM
(SELECT STR
 FROM
 (SELECT REPLACE(sys_connect_by_path( n,',' ),',') str
    FROM (SELECT  n FROM NUM) YourTable
  CONNECT by NOCYCLE n != PRIOR n
----- The following code can be used to speed up the performance,
----- The SQL Solution works without it.
--------------------------------------------------------------
  AND CASE WHEN LEVEL = 1
	   THEN MOD (N, 2 )
	   ELSE 1
      END  = 1
  AND CASE WHEN LEVEL = 5
	   THEN MOD(N,5)
	    ELSE 0
      END  = 0
------------------------------------------------------------
  AND CASE WHEN MOD(PRIOR N,2) = 0 AND MOD(N,2) = 1
           THEN 1
           WHEN MOD (PRIOR N,2) = 1 AND MOD(N,2) = 0
	   THEN 1
	  END = 1
)
WHERE length(str) = 9) A, (SELECT n FROM NUM) B
WHERE MOD(TO_NUMBER(SUBSTR(A.STR,0, B.n)),B.n) = 0
GROUP BY A.STR
HAVING COUNT(*) = 9
);

       NUM
----------
 381654729

Elapsed: 00:00:00.06

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.