--This script allows you to find unique pairs in a set. You can check

--results by using the Counting Rule for Combinations

--C = n!/(r!(n-r)!). For example, how many combinations of 2 can be made

--out of a set of 4. 4!/(2!(4-2)!)=6 pairs of 2. This script uses a union to block the select

--statements together. You lose your ID numbers in seperate fields here but

--you have the rest together all in one concise statement. A big improvement

--in performance when compared to the original ideas in solving this problem.

--This is for comparison with the T-SQL script. My starting point with DB2.

--By C. Eric Cashon

CREATE TABLE PairsTest (

ID int NOT NULL

GENERATED ALWAYS AS IDENTITY

(START WITH 1, INCREMENT BY 1, CACHE 10),

WellID varchar(10),

ChemName varchar(30));

INSERT INTO PairsTest(WellID, ChemName) VALUES ('A01', 'Parrot')

,('A02', 'Dog')

,('A03', 'Cat')

,('A04', 'Elephant')

,('A05', 'Iguana')

,('A06', 'Turtle')

,('A07', 'Shark')

,('A08', 'Frog')

,('A09', 'Toad')

,('A10', 'Crow');

CREATE VIEW PairsTest1 (WellID, ChemName)

AS

SELECT WellID, ChemName FROM PairsTest

UNION

SELECT T1.WellID || T2.WellID as Combo,

T1.ChemName || ', ' || T2.ChemName as Names FROM

PairsTest T1, PairsTest T2

WHERE T1.ID < T2.ID

UNION

SELECT T1.WellID || T2.WellID || T3.WellID as Combo,

T1.ChemName || ', ' || T2.ChemName || ', ' || T3.ChemName as Names FROM

PairsTest T1, PairsTest T2, PairsTest T3

WHERE T1.ID < T2.ID AND T2.ID < T3.ID

AND T1.ID < T3.ID;

--C = 10!/(1!(10 - 1)!)

-- = 10

--C = 10!/(2!(10 - 2)!)

-- = 45

--C = 10!/(3!(10 - 3)!)

--C = 120

--Total = 175

SELECT * FROM PairsTest1;

SELECT COUNT(*) FROM PairsTest1 WHERE WellID LIKE'___';

SELECT COUNT(*) FROM PairsTest1 WHERE WellID LIKE'______';

SELECT COUNT(*) FROM PairsTest1 WHERE WellID LIKE'_________';

--Clean up

DROP VIEW PairsTest1;

DROP TABLE PairsTest;