To this end I've created a new table called LOT_GENEALOGY_WHEREUSED_TESTS;
Lot_Genealogy_WhereUsed_Tests Table Description |
To create tests the following SQL will insert an existing Lot Genealogy into the testing table;
INSERT INTO LOT_GENEALOGY_WHEREUSED_TESTS
SELECT 'ATR002', -- test_Ref
'Product lot (166449) consists of two items (038003 and 038001)', -- test_description
-- data from Lot Genealogy
MASTER_LOT_NUMBER,
INGRED_LOT_NUMBER,
INGRED_ITEM_NUMBER,
INGRED_ITEM_DESCRIPTION,
INGRED_ITEM_TYPE_CODE,
BATCH_NUMBER,
PRODUCT_LOT_NUMBER,
PRODUCT_ITEM_NUMBER,
PRODUCT_ITEM_DESCRIPTION,
PRODUCT_ITEM_TYPE_CODE
FROM LOT_GENEALOGY_WHEREUSED LGW
WHERE 1 = 1
AND LGW.MASTER_LOT_NUMBER = '0490/0002';
This SQL is taking the lot genealogy for Master Lot Number 0490/0002 and copying it into the testing table, adding a test reference (ATR002) and a test description so we know what the test is supposed to be checking for.
That, as they say, was the easy bit. Now we need to create some SQL that is capable of running a test and returning a result. Our reporting tool (in case you can't tell from my other blog posts!) is SQL Server Reporting Services (SSRS) hence I'm going to split the SQL into two pieces, one to give me a list of all the tests and the other to run an individual test - SSRS will allow me to embed the latter as a sub-report into a report driven by the former.
List of Tests SQL
This was by far the easiest of the two;SELECT DISTINCT LGWT.TEST_REF VALUE,
LGWT.Test_Ref || ' (Lot ' || LGWT.MASTER_LOT_NUMBER || ')' LABEL
FROM LOT_GENEALOGY_WHEREUSED_TESTS LGWT
ORDER BY 1, 2
This returns something similar to;
List of Tests Generated Using SQL |
Run A Test SQL
This is slightly larger but here's the code then I'll try and explain it;SELECT :Test_Ref "Test Ref",
(SELECT TEST_DESCRIPTION
FROM LOT_GENEALOGY_WHEREUSED_TESTS LGWT1
WHERE LGWT1.TEST_REF = :Test_Ref
AND ROWNUM = 1) "Test Description",
TR.Test_Row_Count "Test Row Count",
TR.Cache_Row_Count "Cache Row Count",
TR.Union_Row_Count "Union Row Count",
CASE
WHEN TR.Test_Row_Count = TR.Cache_Row_Count AND
TR.Cache_Row_Count = TR.Union_Row_Count THEN
'PASS'
ELSE
'FAIL'
END "Test Result"
FROM (SELECT (SELECT COUNT(*)
FROM LOT_GENEALOGY_WHEREUSED_TESTS LGWT1
WHERE LGWT1.TEST_REF = :Test_Ref) Test_Row_Count,
(SELECT COUNT(*)
FROM LOT_GENEALOGY_WHEREUSED LGW
WHERE LGW.MASTER_LOT_NUMBER =
(SELECT MASTER_LOT_NUMBER
FROM LOT_GENEALOGY_WHEREUSED_TESTS
WHERE TEST_REF = :Test_Ref
AND ROWNUM = 1)) Cache_Row_Count,
(SELECT COUNT(*)
FROM (SELECT MASTER_LOT_NUMBER,
INGRED_LOT_NUMBER,
INGRED_ITEM_NUMBER,
INGRED_ITEM_DESCRIPTION,
INGRED_ITEM_TYPE_CODE,
BATCH_NUMBER,
PRODUCT_LOT_NUMBER,
PRODUCT_ITEM_NUMBER,
PRODUCT_ITEM_DESCRIPTION,
PRODUCT_ITEM_TYPE_CODE
FROM LOT_GENEALOGY_WHEREUSED_TESTS LGWT1
WHERE LGWT1.TEST_REF = :Test_Ref
UNION
SELECT MASTER_LOT_NUMBER,
INGRED_LOT_NUMBER,
INGRED_ITEM_NUMBER,
INGRED_ITEM_DESCRIPTION,
INGRED_ITEM_TYPE_CODE,
BATCH_NUMBER,
PRODUCT_LOT_NUMBER,
PRODUCT_ITEM_NUMBER,
PRODUCT_ITEM_DESCRIPTION,
PRODUCT_ITEM_TYPE_CODE
FROM LOT_GENEALOGY_WHEREUSED LGW
WHERE LGW.MASTER_LOT_NUMBER =
(SELECT MASTER_LOT_NUMBER
FROM LOT_GENEALOGY_WHEREUSED_TESTS
WHERE TEST_REF = :Test_Ref
AND ROWNUM = 1))) Union_Row_Count
FROM DUAL) TR
As you can see it takes a single parameter, the Test Reference Number. How it works is it counts the number of records in the cache, counts the number of records in the test table, and then does a select of all the records in the test table and, using a straight UNION, all the records in the cache. Because of the way UNIONs work (stripping out duplicates) the COUNT of the number of records returned by the UNION should be the same as the number of records in each of the other two queries. If they are all the same the TEST_RESULT is 'PASS' otherwise it's 'FAIL'.
NOTE: I'm sure this could be done a lot more efficiently but to be honest given the relative sizes of the tables I don't think you'll be sitting round too long for a result. On our system it takes less then .02 of a second. Your mileage will vary, but probably not by much!
Now that I've got the SQL I've setup a simple SSRS report to display the result for a single test back to me;
SSRS Report Showing A Single Test Result |
The master report looks like this (in Report Builder 3);
SSRS Master Reporting Showing All Tests |
When executed the report appears like this;
SSRS Testing Report - Final Result |
No comments:
Post a Comment