OraQA

The following is an interesting problem posted by mathforum.org:

Find out how many 3 digit numbers have the digital sum of nine?

————————————————— 10G SQL Solution—————————————————–

SELECT to_number(str_num) as str_num,
       CASE WHEN ROWNUM = 1
	    THEN count(*) over ( )
       END AS CNT
FROM
(SELECT replace(sys_connect_by_path(num, ','), ',') str_num
 FROM (SELECT LEVEL - 1 num FROM dual CONNECT BY LEVEL <=10)
 WHERE LEVEL = 3
 CONNECT BY LEVEL < 4
 AND CASE LEVEL
          WHEN 2
          THEN CASE WHEN (PRIOR num != 0) AND (PRIOR num + num <= 9)
		    THEN 1 END
	  WHEN 3
	  THEN CASE WHEN CONNECT_BY_ROOT(num) + PRIOR num + num = 9
		    THEN 1 END
	  ELSE 1  END = 1
 AND PRIOR dbms_random.string('p', 20) IS NOT NULL
);

STR_NUM CNT
———- ———-
108 45
117
126
135
144
153
162
171
180
207
216
225
234
243
252
261
270
306
315
324
333
342
351
360
405
414
423
432
441
450
504
513
522
531
540
603
612
621
630
702
711
720
801
810
900

45 rows selected.

SQL>

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