--Here
is an example of a Percent of Control function for DB2. A useful
--calculation
for data that is streamed off of a plate reader in a scientific
--lab.
--By
C. Eric Cashon
--Functions
need to be created in the Development Center. Then the script
--following
the functions can be run in the command editor.
CREATE
FUNCTION PosControl()
RETURNS REAL
LANGUAGE SQL
BEGIN ATOMIC
DECLARE Test1 REAL;
SET Test1 = RAND() * 1000;
WHILE (Test1 < 800 OR Test1 > 900) DO
SET Test1 = RAND() * 1000;
END WHILE;
RETURN Test1;
END
CREATE
FUNCTION NegControl()
RETURNS REAL
LANGUAGE SQL
BEGIN ATOMIC
DECLARE Test1 REAL;
SET Test1 = RAND() * 1000;
WHILE (Test1 < 100 OR Test1 > 200) DO
SET Test1 = RAND() * 1000;
END WHILE;
RETURN Test1;
END
--DROP
FUNCTION PosControl();
--DROP
FUNCTION NegControl();
--If
you create a table without a primary key constraint it won't have
--a
index automatically built. Try this script with and without the
--primary
key constraint and see the difference in the query execution time.
--It
gives you an idea of what the query optimizer can do. If you have
--a
slow computer you might want to cut the record set down to 960 before
--you
try this. Test computer, please.
CREATE TABLE RandomData (TestID INTEGER NOT
NULL PRIMARY KEY, TestData REAL);
-- generate 9600 records. Insert test data
values in a suitable pattern.
INSERT INTO RandomData
WITH Generate(TestID) AS (VALUES(1) UNION ALL
SELECT TestID+1 FROM Generate WHERE TestID < 9600 )
SELECT TestID, (CASE MOD((96 + TestID),96)
WHEN 1 THEN PosControl()
WHEN 2 THEN PosControl()
WHEN 3 THEN PosControl()
WHEN
4 THEN PosControl()
WHEN 5
THEN NegControl()
WHEN 6
THEN NegControl()
WHEN 7
THEN NegControl()
WHEN 8
THEN NegControl()
ELSE (RAND() * 1000) END) AS TestData
FROM Generate;
--SELECT * FROM RandomData;
--This
query will compute percent of control off of the randomly generated
--numbers.
This is the DB2 example. Compare it with the T-SQL example. Also
--there
are examples of how to do this calculation in Excel and Access.
--Always
good to know a problem has many solutions instead of none at all.
SELECT
T2.TestID, T2.TestData, (((T2.TestData - T2.Neg)/(T2.Pos - T2.Neg)) *
100)
AS POC
FROM
(SELECT T1.TestID, T1.TestData, (SELECT CASE
WHEN
MOD(T1.TestID, 96) <> 0 THEN
(SELECT
(SUM(T3.TestData)/ 4) FROM RandomData T3
WHERE
T3.TestID >= (T1.TestID / 96) * 96 + 1
AND
T3.TestID <= (T1.TestID / 96) * 96 + 4
)
ELSE
(SELECT
(SUM(T4.TestData)/ 4) FROM RandomData T4
WHERE
T4.TestID >= ((T1.TestID - 1) / 96) * 96 + 1
AND
T4.TestID <= ((T1.TestID - 1) / 96) * 96 + 4
)
END AS Pos
FROM SYSIBM.SYSDUMMY1),
(SELECT CASE
WHEN
MOD(T1.TestID, 96) <> 0 THEN
(SELECT
(SUM(T5.TestData) / 4) FROM RandomData T5
WHERE T5.TestID >= (T1.TestID /
96) * 96 + 5
AND
T5.TestID <= (T1.TestID / 96) * 96 + 8
)
ELSE
(SELECT (SUM(T6.TestData)
/ 4) FROM RandomData T6
WHERE
T6.TestID >= ((T1.TestID - 1) / 96) * 96 + 5
AND
T6.TestID <= ((T1.TestID - 1) / 96) * 96 + 8
)
END AS Neg
FROM SYSIBM.SYSDUMMY1)
FROM RandomData T1) AS T2;
--Clean
up.
DROP
TABLE RandomData;