Results 1 to 1 of 1
  1. #1
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483

    Unanswered: BITS(a UDF returns bit representation of a number): An example of LISTAGG

    LISTAGG is a new function added in DB2 for LUW 9.7 FP4.

    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE OR REPLACE FUNCTION
    bits
    ( in_number SMALLINT )
      RETURNS CHAR(16)
      LANGUAGE SQL
      DETERMINISTIC
      NO EXTERNAL ACTION
    RETURN
    SELECT LISTAGG(
              SUBSTR(
                 '...0000000100100011010001010110011110001001101010111100110111101111'
               , LOCATE( SUBSTR(HEX(in_number) , k , 1) , '0123456789ABCDEF' ) * 4
               , 4
              )
           ) WITHIN GROUP (
              ORDER BY SUBSTR( HEX(INT(1)) , 2 , 1 ) -- IF   little endian,
                       * ( (k - 1) / 2 ) DESC        -- THEN reverse byte(= two hex digits) order.
                     , k
           )
     FROM  (VALUES 1 , 2 , 3 , 4) k(k)
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Sample usage:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     test_data(in_number) AS (
    SELECT SMALLINT(in_number)
     FROM  (VALUES
              ( 32767) , ( 16383)
            , (  4096) , (   118)
            , (    63) , (    16)
            , (     2) , (     1)
            , (     0) , (    -1)
            , (    -2) , (    -3)
            , (   -64) , (-32768)
           ) in(in_number)
    )
    SELECT in_number
         , HEX(in_number)  AS hex
         , BITS(in_number) AS bits
     FROM  test_data
    ;
    ------------------------------------------------------------------------------
    
    IN_NUMBER HEX  BITS            
    --------- ---- ----------------
        32767 FF7F 0111111111111111
        16383 FF3F 0011111111111111
         4096 0010 0001000000000000
          118 7600 0000000001110110
           63 3F00 0000000000111111
           16 1000 0000000000010000
            2 0200 0000000000000010
            1 0100 0000000000000001
            0 0000 0000000000000000
           -1 FFFF 1111111111111111
           -2 FEFF 1111111111111110
           -3 FDFF 1111111111111101
          -64 C0FF 1111111111000000
       -32768 0080 1000000000000000
    
      14 record(s) selected.
    Test data was extracted from "Figure 337, BIT_DISPLAY function example" in "DB2 V9.7 Cookbook" by Graeme Birchall.
    Graeme Birchall
    Last edited by tonkuma; 05-26-11 at 09:28.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •