1 2 Previous Next 28 Replies Latest reply: Jun 14, 2008 9:54 AM by 486393 Go to original post RSS
      • 15. Re: SQL puzzle :)
        NicloeiW
        ok, both are same ;-)
        can i have few links which explains reagarding xml things
        • 16. Re: SQL puzzle :)
          MichaelS
          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
            dan ke
            • 18. Re: SQL puzzle :)
              Nicolas.Gasparotto
              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
                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 :)
                  Rene W.
                  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
                    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
                      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
                        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
                          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
                            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
                              Interesting.

                              What kind of user defined aggregate function did you built? With or without collect?
                              • 27. Re: SQL puzzle :)
                                ascheffer
                                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
                                  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