For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
SQL> create or replace function sum_prod(p_val in number) return number is 2 l_val Varchar2(500) := p_val; 3 l_sum number := 0; 4 l_n1 number; 5 l_n2 number; 6 l_product number := 1; 7 l_first number; 8 begin 9 for i in 1..length(p_val) 10 loop 11 l_first := substr(l_val, 1, 1); 12 l_sum := l_sum + l_first; 13 l_product := l_product * l_first; 14 l_val := substr(l_val, 2); 15 end loop; 16 return(l_sum + l_product); 17 end; 18 / Function created. SQL> select rno from ( select rownum rno 2 from dual 3 connect by level <= 100) 4 where rno - reverse(to_char(rno)) = sum_prod(rno); RNO ----- 63 SQL> select rno from ( select rownum rno 2 from dual 3 connect by level <= 1000) 4 where rno - reverse(to_char(rno)) = sum_prod(rno); RNO ----- 63 726 SQL> select rno from ( select rownum rno 2 from dual 3 connect by level <= 10000) 4 where rno - reverse(to_char(rno)) = sum_prod(rno); RNO ----- 63 726 8937
I think SQL experts can write it more elegantly.
Hi,
The brute force won't help too much in your case. You may want to exclude at least the number where the last digit is higher than the first. And still, with such big number, I'm not sure SQL and PL/SQL will be very efficient. Lastly, even if that takes half ad day to finish, I'm sure it's not a scheduled job, after running it once, it's finish.
Anyway, here below my first try on my laptop :
SQL> create or replace type nb_obj as object (n1 number, n2 number, n3 number, n4 number); 2 / Type created. Elapsed: 00:00:00.00 SQL> SQL> create or replace type nb_list as table of nb_obj; 2 / Type created. Elapsed: 00:00:00.04 SQL> SQL> create or replace function f_maths (p_max number) return nb_list pipelined is 2 x number:=0; 3 y number; 4 t number; 5 z number; 6 7 begin 8 loop 9 --X is a positive number 10 x := x+1; 11 if substr(x,-1,1) > substr(x,1,1) 12 then x := x+11-substr(x,-1,1); 13 end if; 14 exit when x > p_max; 15 16 y := null; 17 t := 0; 18 z := 1; 19 for j in 1..length(x) loop 20 --Y is the reverse of X 21 y:=y||to_char(substr(x,-j,1)); 22 --T is the sum of each number which X has 23 t:=t+substr(x,-j,1); 24 --Z is the product of each number which X has 25 z:=z*substr(x,-j,1); 26 exit when y > substr(x,1,j); 27 end loop; 28 --X - Y = T + Z 29 if x - y = t + z then 30 pipe row (nb_obj(x,y,t,z)); 31 end if; 32 end loop; 33 end; 34 / Function created. Elapsed: 00:00:00.04 SQL> show err No errors. SQL> select * 2 from table(f_maths(10000)); N1 N2 N3 N4 ---------- ---------- ---------- ---------- 63 36 9 18 726 627 15 84 8937 7398 27 1512 Elapsed: 00:00:00.06 SQL> SQL> select * 2 from table(f_maths(100000)); N1 N2 N3 N4 ---------- ---------- ---------- ---------- 63 36 9 18 726 627 15 84 8937 7398 27 1512 Elapsed: 00:00:00.60 SQL> SQL> select * 2 from table(f_maths(1000000)); N1 N2 N3 N4 ---------- ---------- ---------- ---------- 63 36 9 18 726 627 15 84 8937 7398 27 1512 Elapsed: 00:00:07.01 SQL> SQL> select * 2 from table(f_maths(10000000)); N1 N2 N3 N4 ---------- ---------- ---------- ---------- 63 36 9 18 726 627 15 84 8937 7398 27 1512 Elapsed: 00:01:21.81 SQL> SQL> select * 2 from table(f_maths(100000000)); N1 N2 N3 N4 ---------- ---------- ---------- ---------- 63 36 9 18 726 627 15 84 8937 7398 27 1512 Elapsed: 00:15:36.81
Nicolas.
I replaced "SUM_PROD" function with wateenmooiedag's query:
2577576
SQL> select rno from ( select rownum rno 2 from dual 3 connect by level <= 1000) 4 where rno - reverse(to_char(rno)) = 5 (select to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select ' 6 ||regexp_replace(rno,'(.)','\1+') || '0'||' 7 s from dual')),'/ROWSET/ROW/S')) 8 + to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select ' 9 ||regexp_replace(rno,'(.)','\1*') || '1'||' 10 p from dual')),'/ROWSET/ROW/P')) 11 from dual); RNO ---------- 63 726 SQL>
Just a new try, ~3 minutes less for hundred million...
SQL> drop type nb_list; Type dropped. Elapsed: 00:00:00.11 SQL> create or replace type nb_obj as object (n1 number, n2 number, n3 number, n4 number); 2 / Type created. Elapsed: 00:00:00.01 SQL> SQL> create or replace type nb_list as table of nb_obj; 2 / Type created. Elapsed: 00:00:00.15 SQL> SQL> create or replace function f_maths (p_min number,p_max number) return nb_list pipelined is 2 x number:=p_min-1; 3 y number; 4 t number; 5 z number; 6 x1 number; 7 x2 number; 8 begin 9 loop 10 --X is a positive number 11 x := x+1; 12 x1 := substr(x,1,floor(length(x)/2)); 13 x2 := substr(x,-floor(length(x)/2)); 14 --dbms_output.put_line(x||' '||x1||' '||x2); 15 if x1 < x2 or length(x2) < length(x1) 16 then x := ceil(x/power(10,length(x2)))*power(10,length(x2)); 17 end if; 18 exit when x > p_max; 19 20 y := null; 21 t := 0; 22 z := 1; 23 for j in 1..length(x) loop 24 --Y is the reverse of X 25 y:=y||to_char(substr(x,-j,1)); 26 exit when y > substr(x,1,j); 27 --T is the sum of each number which X has 28 t:=t+substr(x,-j,1); 29 --Z is the product of each number which X has 30 z:=z*substr(x,-j,1); 31 end loop; 32 --X - Y = T + Z 33 if x - y = t + z then 34 pipe row (nb_obj(x,y,t,z)); 35 end if; 36 end loop; 37 end; 38 / Function created. Elapsed: 00:00:00.04 SQL> show err No errors. SQL> select * 2 from table(f_maths(1,100000000)); N1 N2 N3 N4 ---------- ---------- ---------- ---------- 63 36 9 18 726 627 15 84 8937 7398 27 1512 Elapsed: 00:12:43.79 SQL>
SQL> select * from 2 xmltable('declare function local:reverse($a) 3 { 4 if (string-length($a) != 0) then 5 concat(substring($a,string-length($a),1), local:reverse(substring($a,1,string-length($a)-1))) 6 else () 7 }; (: eof :) 8 declare function local:sum($a) 9 { 10 if (string-length($a) != 0) then 11 xs:integer(substring($a,1,1)) + xs:integer(local:sum(substring($a,2))) 12 else (0) 13 }; (: eof :) 14 declare function local:prod($a) 15 { 16 if (string-length($a) != 0) then 17 xs:integer(substring($a,1,1)) * xs:integer(local:prod(substring($a,2))) 18 else (1) 19 }; (: eof :) 20 for $i in 1 to 10000 21 where $i - local:reverse(xs:string($i)) = local:sum(xs:string($i)) + local:prod(xs:string($i)) 22 return $i' columns x integer path '.') 23 / X ---------- 63 726 8937 Abgelaufen: 00:01:24.51
SQL> select * from (select level x from dual connect by level <= 10000) 2 connect by nocycle level <= length (x) and prior x = x - 1 3 group by x 4 having x - reverse(to_char(x)) = sum(substr(x,level,1)) + round(exp(sum(ln(decode(substr(x,level,1),0,1,substr(x,level,1)))))) 5 order by x 6 / X ---------- 63 726 8937 Abgelaufen: 00:00:02.29
reverse(to_char(x))
You could use
sum(substr(x,level,1)*power(10,level-1))
to keep a supported way. ;-)
How about:
SELECT x FROM ( SELECT level AS x, TO_NUMBER(REVERSE(TO_CHAR(level))) AS y, ( NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '234567890', 'xxxxxxxxxx'), 'x')), 0) * 1 + NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '134567890', 'xxxxxxxxxx'), 'x')), 0) * 2 + NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '124567890', 'xxxxxxxxxx'), 'x')), 0) * 3 + NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123567890', 'xxxxxxxxxx'), 'x')), 0) * 4 + NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123467890', 'xxxxxxxxxx'), 'x')), 0) * 5 + NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123457890', 'xxxxxxxxxx'), 'x')), 0) * 6 + NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123456890', 'xxxxxxxxxx'), 'x')), 0) * 7 + NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123456790', 'xxxxxxxxxx'), 'x')), 0) * 8 + NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123456780', 'xxxxxxxxxx'), 'x')), 0) * 9 + NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123456789', 'xxxxxxxxxx'), 'x')), 0) * 0 ) AS t, ( POWER(1, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '234567890', 'xxxxxxxxxx'), 'x')), 0)) * POWER(2, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '134567890', 'xxxxxxxxxx'), 'x')), 0)) * POWER(3, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '124567890', 'xxxxxxxxxx'), 'x')), 0)) * POWER(4, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123567890', 'xxxxxxxxxx'), 'x')), 0)) * POWER(5, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123467890', 'xxxxxxxxxx'), 'x')), 0)) * POWER(6, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123457890', 'xxxxxxxxxx'), 'x')), 0)) * POWER(7, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123456890', 'xxxxxxxxxx'), 'x')), 0)) * POWER(8, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123456790', 'xxxxxxxxxx'), 'x')), 0)) * POWER(9, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123456780', 'xxxxxxxxxx'), 'x')), 0)) * POWER(0, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123456789', 'xxxxxxxxxx'), 'x')), 0)) ) AS z FROM dual CONNECT BY level < 10000 ) WHERE x - y = t + z ;
Cheers,
Colin
select n1 from ( select to_char( level ) n1 , length( to_char( level ) ) sl from dual where level > reverse( to_char( level ) ) connect by level < 100000 ) where 0 = ( select n1 - sum( substr( n1, level, 1 ) * case level when 1 then 1 when 2 then 10 when 3 then 100 when 4 then 1000 when 5 then 10000 when 6 then 100000 when 7 then 1000000 when 8 then 10000000 when 9 then 100000000 end ) - sum( substr( n1, level, 1 ) ) - round( exp( sum( ln( nullif( substr( n1, level, 1 ), '0' ) ) ) ) ) from dual connect by level <= sl )
select n1 from ( select to_char( level ) n1 , length( to_char( level ) ) sl from dual where level > reverse( to_char( level ) ) connect by level < 100000 ) where 0 = ( select n1 - sum( substr( n1, level, 1 ) * power(10,level-1) ) - sum( substr( n1, level, 1 ) ) - round( exp( sum( ln( nullif( substr( n1, level, 1 ), '0' ) ) ) ) ) from dual connect by level <= sl )
create type myaggImpl as object ( prod NUMBER, static function ODCIAggregateInitialize( sctx IN OUT myaggImpl ) return number, member function ODCIAggregateIterate( self IN OUT myaggImpl, value IN number ) return number, member function ODCIAggregateTerminate( self IN myaggImpl, returnValue OUT number, flags IN number ) return number, member function ODCIAggregateMerge( self IN OUT myaggImpl, ctx2 IN myaggImpl ) return number ); / create or replace type body myaggImpl is static function ODCIAggregateInitialize( sctx IN OUT myaggImpl ) return number is begin sctx := myaggImpl( 1 ); return ODCIConst.Success; end; member function ODCIAggregateIterate( self IN OUT myaggImpl, value IN number ) return number is begin self.prod := self.prod * value; return ODCIConst.Success; end; member function ODCIAggregateTerminate( self IN myaggImpl, returnValue OUT number, flags IN number ) return number is begin returnValue := self.prod; return ODCIConst.Success; end; member function ODCIAggregateMerge(self IN OUT myaggImpl, ctx2 IN myaggImpl ) return number is begin self.prod := self.prod * ctx2.prod; return ODCIConst.Success; end; end; / CREATE FUNCTION myagg( input NUMBER ) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING myaggImpl; /
create type number_table is table of number; / create or replace function product (l_numbers number_table ) return number is l_result number := 1; begin for i in l_numbers.first..l_numbers.last loop l_result := l_result * l_numbers(i); end loop; return l_result; end; / select n1 from ( select to_char( level ) n1 , length( to_char( level ) ) sl from dual where level > reverse( to_char( level ) ) connect by level < 100000 ) where 0 = ( select n1 - sum( substr( n1, level, 1 ) * power(10,level-1) ) - sum( substr( n1, level, 1 ) ) - product(cast(collect(to_number(substr( n1, level, 1 ))) as number_table)) from dual connect by level <= sl ) /