This discussion is archived
1 2 Previous Next 28 Replies Latest reply: Jun 14, 2008 7:54 AM by 486393 Go to original post RSS
  • 15. Re: SQL puzzle :)
    NicloeiW Journeyer
    Currently Being Moderated
    ok, both are same ;-)
    can i have few links which explains reagarding xml things
  • 16. Re: SQL puzzle :)
    MichaelS Guru
    Currently Being Moderated
    can i have link from where i can learn more about xml with sql and they way u used it,
    Some places to start would be

    Using XQuery with Oracle XML DB
    XQuery Tutorials
    XQuery 1.0 and XPath 2.0 Functions and Operators
  • 17. Re: SQL puzzle :)
    NicloeiW Journeyer
    Currently Being Moderated
    dan ke
  • 18. Re: SQL puzzle :)
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    reverse(to_char(x))
    You could use
    sum(substr(x,level,1)*power(10,level-1))
    to keep a supported way.
    ;-)

    Nicolas.
  • 19. Re: SQL puzzle :)
    Colin'tHart Newbie
    Currently Being Moderated
    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
  • 20. Re: SQL puzzle :)
    ReneW. Explorer
    Currently Being Moderated
    Thanks Colin,

    A very straightforward and easy to understand solution.
    My brain still hurts trying to understand Michaels sql statement ;)

    Rene
  • 21. Re: SQL puzzle :)
    ascheffer Expert
    Currently Being Moderated
    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
  • 22. Re: SQL puzzle :)
    598210 Newbie
    Currently Being Moderated
    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.
  • 23. Re: SQL puzzle :)
    486393 Explorer
    Currently Being Moderated
    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
    )
    /
  • 24. Re: SQL puzzle :)
    486393 Explorer
    Currently Being Moderated
    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
                     )
  • 25. Re: SQL puzzle :)
    ascheffer Expert
    Currently Being Moderated
    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
  • 26. Re: SQL puzzle :)
    486393 Explorer
    Currently Being Moderated
    Interesting.

    What kind of user defined aggregate function did you built? With or without collect?
  • 27. Re: SQL puzzle :)
    ascheffer Expert
    Currently Being Moderated
    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;
    /
  • 28. Re: SQL puzzle :)
    486393 Explorer
    Currently Being Moderated
    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
                     )
    /
1 2 Previous Next