## Forum Stats

• 3,851,600 Users
• 2,264,001 Discussions

Discussions

# SQL puzzle :)

13»

• Not the fastest method, but it works.
```       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
)```
Anton
• Thanks to everyone who gave feedback to this thread, pipelined pl/sql solution until now was the fastest for me. But other solutions was great to know, each made me think how alternatives may vary for any kind of question.
• I changed Anton's query a little bit.

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
)
/
• I changed the case statement in power(10,level-1)
```       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
)```
• That's my original version.
The power-function is as fast as the "case version"
The method using "round( exp( sum( ln( nullif( substr( n1, level, 1 ), '0' ) ) ) ) )"
is, to my surprise, faster as using a user-defined aggregrate function.

Anton
• Interesting.

What kind of user defined aggregate function did you built? With or without collect?
• ```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;
/```
• Collect makes it faster, less context switching between sql and pl/sql . Collect works in Oracle 10 and 11.
```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
)
/```
This discussion has been closed.