__Question__:

[Joe Celko's One In Ten Puzzle]

Alan Flancman ran into a problem with some legacy system
data that had been moved over to a SQL database.
The table looked like this:

CREATE TABLE MyTable
(keycol INTEGER NOT NULL,
f1 INTEGER NOT NULL,
f2 INTEGER NOT NULL,
...
f10 INTEGER NOT NULL,
);

The columns

`f1` through

`f10` were an attempt to flatten out an
array into a table. What he wanted was an elegant way to
test against the

`f1` through

`f10` columns to find the rows that
had exactly one non-zero in their

`f`-columns.

How many different approaches can you find?

*Answer*:

*Solution One:*

You could use the Sign() function in Sybase and other SQL
products. This function returns -1, 0, or +1 if the argument
is negative, zero, or positive respectively. Assuming that
your numbers are zero or greater, you simply write:

SELECT *
FROM MyTable
WHERE Sign(f1) + Sign(f2) + ... + Sign(f10) = 1;

to find a single non-zero value. If you can have negative values,
then make the functions

`Sign(ABS(fn))`.

The Sign() function can be written with the CASE expression in SQL-92 as:

CASE WHEN x > 0 THEN 1
WHEN x = 0 THEN 0
WHEN x < 0 THEN -1
OTHERWISE NULL END

*Solution Two:*

Since the fields are really an attempt to fake an array, you
should normalize this table into 1NF, like so:

CREATE TABLE Foobar
(keycol INTEGER NOT NULL,
i INTEGER NOT NULL CHECK (i BETWEEN 1 AND 10),
f INTEGER NOT NULL,
PRIMARY KEY (keycol, i));

The extra column

`i` is really the subscript for the array.
You now view the problem as finding an entity which has
exactly nine zero valued

`f`s, instead of as finding an
entity which has exactly one non-zero valued

`f`. That is suddenly easy:

SELECT keycol
FROM Foobar
WHERE f = 0
GROUP BY keycol
HAVING COUNT(*) = 9;

You can create a VIEW that has the structure of Foobar,
but things are going to run pretty slow unless you have a good optimizer:

CREATE VIEW Foobar (keycol, f)
AS SELECT keycol, f1 FROM MyTable
UNION
SELECT keycol, f2 FROM MyTable
UNION
...
UNION
SELECT keycol, f10 FROM MyTable;

*Solution Three:*

This depends on a feature of SQL-92 that is not generally available yet.

SELECT *
FROM MyTable
WHERE (f1, f2, ... , f10) IN
((f1, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(0, f2, 0, 0, 0, 0, 0, 0, 0, 0),
....
(0, 0, 0, 0, 0, 0, 0, 0, 0, f10));

In SQL-92, you can use row constructors in comparison predicates.
The IN predicate expands into a sequence of OR-ed equality
predicates. The row-wise version of equality is then done on a
position by position basis, where all corresponding values must be equal.

*Puzzle provided courtesy of:
*

Joe Celko

71062.1056@compuserve.com