Contents
The first column returned by an ISOK_QUERIES.Query, the ID column, must return a unique value. That is, unique within the set of rows produced by the query.
There are two possible approaches to take when deciding what value to return in the query's ID column. The first is to take a minimalist approach and use the shortest possible unique value. This has the advantage of being easy to type when manually writing SQL that contains the ID.
This is the approach taken by the example which describes testing for customers that have no orders, reproduced here:
SELECT customers.id
, 'Customer ' || customers.id || ' has no related ORDERS'
, NULL
FROM customers
WHERE NOT EXISTS (SELECT 1
FROM orders
WHERE orders.customerid = customers.id);
An alternate approach is to make the ID column descriptive. The advantage of this approach is that it provides a summary of the problem in the ID column, so that the second column the query returns, the Msg column, need not be examined when cursorily looking over Isok's output. This can be important if the second column, the Msg column, contains a variety of additional information that might be useful in resolving the issue.
Here is the test for customers without orders, re-written so that the ID column summarizes the problem and the Msg column provides useful detail:
SELECT 'Customer ' || customers.id || ' has no related ORDERS'
, 'The problem customer is: Key (ID) = ' || customers.id
|| '), Value (Name) = (' || customers.name
|| '), Value (Country) = (' || customers.country
|| '), Value (State) = (' || customers.state
|| ')'
, NULL
FROM customers
WHERE NOT EXISTS (SELECT 1
FROM orders
WHERE orders.customerid = customers.id);
Page generated: 2025-07-13T14:19:35-05:00.