How to compute numbers that are both divisible by seven and when the digits are reversed are still divisible by seven in a SQL Statement?
January 16th, 2007 By Frank Zhou
The following SQL Model Clause pattern can be used to compute the sum and max of all the integers between 1 and the user input number that are both divisible by seven and when the decimal digits are reverse, are still divisible by seven:
SQL> variable input number
SQL> exec :input := 10000
PL/SQL procedure successfully completed.
SELECT SUM(num) AS SUM, MAX(num) AS MAX
FROM
(
SELECT num
FROM dual
MODEL
DIMENSION BY ( 0 pos)
MEASURES (CAST (NULL AS number) num )
RULES ITERATE (80000000)
UNTIL ( (iteration_number+1) * 7 >= :input )
(
num[iteration_number] = iteration_number * 7
)
)
WHERE MOD(TO_NUMBER(REVERSE(TO_CHAR(num))),7) = 0
SUM MAX
———- ———-
1029567 9954
——————————————-9I Solution—————–
SELECT SUM(num) AS SUM, MAX(num) AS MAX
FROM (SELECT LEVEL * 7 num FROM dual CONNECT BY LEVEL * 7 <= :input )
WHERE MOD( TO_NUMBER( REVERSE(TO_CHAR(num))),7) = 0;
SUM MAX
———- ———-
1029567 9954