# Thread: Relational Division using LISTAGG

1. Registered User
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. Registered User
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
•