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

    Unanswered: Relational Division using LISTAGG

    At least two ways are popular to acheve Relational Division.

    1) Relational Division allowing residue.
    devidend(x,y) / divisor(y) = x

    Example 1-1: Nested NOT EXISTS
    Code:
    SELECT DISTINCT
           x
     FROM  devidend a
     WHERE NOT EXISTS
           (SELECT 0
             FROM  divisor b
             WHERE NOT EXISTS
                   (SELECT 0
                     FROM  devidend c
                     WHERE c.x = a.x
                       AND c.y = b.y
                   )
           )
    ;
    Example 1-2: JOIN - HAVING - COUNT
    Code:
    SELECT a.x
     FROM  devidend a
     INNER JOIN
           divisor  b
       ON  b.y = a.y
     GROUP BY
           a.x
     HAVING
           COUNT(*) = (SELECT COUNT(*) FROM divisor)
    ;

    Here is another way by using LISTAGG
    (and column names in a pattern string of a LIKE predicate which was newly supported in DB2 9.7 PF4 for LUW).

    Example 1-3: LISTAGG
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /**************************************************
    ********** Start of test data 1.         **********
    **************************************************/
     divisor(y) AS (
    VALUES
      ('B') , ('C') , ('D') , ('F') , ('G')
    )
    , devidend(x , y) AS (
    VALUES
    -- x = 1: lack(D)
      (1 , 'B') , (1 , 'C')             , (1 , 'F') , (1 , 'G')
    -- x = 2: Exact match
    , (2 , 'B') , (2 , 'C') , (2 , 'D') , (2 , 'F') , (2 , 'G')
    -- x = 3: surplus(A,E)
    , (3 , 'B') , (3 , 'C') , (3 , 'D') , (3 , 'F') , (3 , 'G')
    , (3 , 'A') , (3 , 'E')
    -- x = 4: lack(G) and surplus(Go), same number of rows
    , (4 , 'B') , (4 , 'C') , (4 , 'D') , (4 , 'F') , (4 , 'Go')
    )
    /**************************************************
    **********   End of test data 1.         **********
    **************************************************/
    SELECT x
     FROM  devidend
     GROUP BY
           x
     HAVING
           LISTAGG( y || ':' )
              WITHIN GROUP( ORDER BY y )
           LIKE
           (SELECT '%' ||
                   CAST( 
                      LISTAGG( y || ':%' )
                         WITHIN GROUP( ORDER BY y )
                      AS VARCHAR(3999)
                   )
             FROM  divisor
           )
    ;
    ------------------------------------------------------------------------------
    
    X          
    -----------
              2
              3
    
      2 record(s) selected.

    2) Exact Division.
    devidend(x,y) / divisor(y) = x

    Example 2-1: LISTAGG
    Code:
    WITH
    /**************************************************
    ********** Start of test data 1.         **********
    **************************************************/
    ...
    ...
    /**************************************************
    **********   End of test data 1.         **********
    **************************************************/
    SELECT x
     FROM  devidend
     GROUP BY
           x
     HAVING
           LISTAGG( y , ':' )
              WITHIN GROUP( ORDER BY y )
           =
           (SELECT LISTAGG( y , ':' )
                      WITHIN GROUP( ORDER BY y )
             FROM  divisor
           )
    ;
    ------------------------------------------------------------------------------
    
    X          
    -----------
              2
    
      1 record(s) selected.

    3) Relational Division allowing residue.
    Return with y in devidend.
    devidend(x,y) / divisor(y) = x,y

    Example 3-1: Nested NOT EXISTS
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /**************************************************
    ********** Start of test data 1.         **********
    **************************************************/
    ...
    ...
    /**************************************************
    **********   End of test data 1.         **********
    **************************************************/
    SELECT x , y
     FROM  devidend a
     WHERE NOT EXISTS
           (SELECT 0
             FROM  divisor b
             WHERE NOT EXISTS
                   (SELECT 0
                     FROM  devidend c
                     WHERE c.x = a.x
                       AND c.y = b.y
                   )
           )
     ORDER BY
           x , y
    ;
    ------------------------------------------------------------------------------
    
    X           Y 
    ----------- --
              2 B 
              2 C 
              2 D 
              2 F 
              2 G 
              3 A 
              3 B 
              3 C 
              3 D 
              3 E 
              3 F 
              3 G 
    
      12 record(s) selected.
    Example 3-2: LISTAGG (Return with y in devidend and mark column('*': not in divisor)).
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /**************************************************
    ********** Start of test data 2.         **********
    **************************************************/
     divisor(y) AS (
    VALUES
      ('Base') , ('C') , ('D') , ('F') , ('G')
    )
    , devidend(x , y) AS (
    VALUES
    -- x = 1: lack(D)
      (1 , 'Base') , (1 , 'C')             , (1 , 'F') , (1 , 'G')
    -- x = 2: Exact match
    , (2 , 'Base') , (2 , 'C') , (2 , 'D') , (2 , 'F') , (2 , 'G')
    -- x = 3: surplus(A,Bas,E)
    , (3 , 'Base') , (3 , 'C') , (3 , 'D') , (3 , 'F') , (3 , 'G')
    , (3 , 'A') , (3 , 'Bas') , (3 , 'E')
    -- x = 4: lack(G) and surplus(Go), same number of rows
    , (4 , 'Base') , (4 , 'C') , (4 , 'D') , (4 , 'F') , (4 , 'Go')
    )
    /**************************************************
    **********   End of test data 2.         **********
    **************************************************/
    SELECT x , y
         , LEFT('*' , 1 - SIGN( LOCATE(y || ':' , pattern) ) ) AS mark
     FROM  (SELECT x , y
                 , LISTAGG( y || ':' )
                      OVER( PARTITION BY x
                                ORDER BY y
                                 ROWS BETWEEN UNBOUNDED PRECEDING
                                          AND UNBOUNDED FOLLOWING
                          ) AS list_y
             FROM  devidend
           ) a
     INNER JOIN
           (SELECT '%' ||
                   CAST(
                      LISTAGG( y || ':%' )
                         WITHIN GROUP( ORDER BY y )
                      AS VARCHAR(3999)
                   )
             FROM  divisor
           ) b(pattern)
       ON  a.list_y LIKE b.pattern
     ORDER BY
           x , y
    ;
    ------------------------------------------------------------------------------
    
    X           Y    MARK
    ----------- ---- ----
              2 Base     
              2 C        
              2 D        
              2 F        
              2 G        
              3 A    *   
              3 Bas  *   
              3 Base     
              3 C        
              3 D        
              3 E    *   
              3 F        
              3 G        
    
      13 record(s) selected.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I saw this rule in LISTAGG - IBM DB2 9.7 for Linux, UNIX, and Windows

    Rules

    ...
    ...
    LISTAGG cannot be used as part of an OLAP specification (SQLSTATE 42887).
    But, I used an OLAP specification for a LISTAGG in my Example 3-2.

    So, there might be some exceptions to the rule,
    or did I misunderstood the description?

Posting Permissions

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