How to get numbers that its summation is equal to specific result

SA,
Today I will present plsql function that return all available numbers that its summation is equal to specific result with condition that numbers formed of specific count of  digits.

Prerequisites
We will create collection (table of varchar2) to be as output result of function
/*****************************************************************
/*   That type will output of function[table of varchar2]
/****************************************************************/

CREATE OR REPLACE TYPE varchar2_nt AS TABLE OF VARCHAR2 (30);

Function Code
/****************************************************************
/*author           : Mahmoud Ahmed El-sayed
/*Email            : mahmoud_ahmed01@yahoo.com
/*creation date    : 10/07/2011
/*Function Purpose : That function used to get the number from n digit that its
/*                   addition is equal to the input result
/*$parameters      :
/*         in_result      ==> Summation of numbers
/*         in_digit_count ==> count digit
/****************************************************************/

CREATE OR REPLACE FUNCTION sum_digit (
   in_result        NUMBER,
   in_digit_count   PLS_INTEGER
)
   RETURN varchar2_nt
IS
   ret_value   varchar2_nt;
BEGIN
   SELECT numbers.RESULT
   BULK COLLECT INTO ret_value
     FROM (SELECT     LTRIM (SYS_CONNECT_BY_PATH (num, '+'),
                             '+'
                            ) addition_equation,
                      REPLACE (SYS_CONNECT_BY_PATH (num, '.'), '.') RESULT
                 FROM (SELECT     LEVEL - 1 num
                             FROM DUAL
                       CONNECT BY LEVEL <= 10)
                WHERE LEVEL = in_digit_count
           CONNECT BY LEVEL <= in_digit_count) numbers
    WHERE dbms_aw.eval_number (numbers.addition_equation) = in_result;

   RETURN ret_value;
END;



Test the Function
SELECT COLUMN_VALUE
  FROM TABLE (sum_digit (9, 3));

The output will be
009
018
027
036
045
054
063
072
081
090
108
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

Thanks
Mahmoud A. El-Sayed

Popular posts from this blog

ADF : Get Current Logged User Name

OAF : Get Current Row in Table

ADF : Working with ViewCriteria