DBMS, February 1998

Enjoying NT

I think Tim Matthewsý overview of NTýs suitability as a DBMS platform in the December issue of DBMS (see "Running on NT,") is excellent ý technical, terse, accurate, sensible, and useful. A nitpick about RPC history: The author credits Sun Microsystems with early work predating the OSF. I believe Apollo deserves equal or greater credit for pre-OSF development of RPC, and that it was the Apollo RPC and its refinements (also pre-OSF) at HP and Digital that contributed the robust features that distinguish the OSF RPC from Sunýs.

Howard A. Markham,
howardm@mitre.org

I think that the article written by Tim Matthews was one of the best that I have read this year on a subject in most of our minds at present. It is intelligent, succinct, and an eminently enjoyable read; more from this gentleman, please!

Dr. R.J. Kirkland
aq79@cityscape.co.uk

Puzzle...Solved!

About Joe Celkoýs puzzle in the August issue of DBMS: You have to create a table containing a list of all the States in a two-letter column. In my solution you have to first find out how many ýstatesý are in every field. You can use the statement (char_length(T1.StateList)+1)/3 only if you are sure that every ýstateý in the string is two characters long. Another way could be to use a function that counts number of commas into the string and then adds one to the sum.

SELECT T1.STATE_LIST
FROM MyTable AS T1
WHERE (CHAR_LENGTH(T1.StateList)+1)/3 <>
(SELECT COUNT(*) 
FROM StateCodes AS S1
WHERE T1.State_List LIKE ý%ý || S1.State || ý%ý); 

Andrea Marconi
marconi@ilink.it

I got more answers to this puzzle than Iýve gotten in a long time to anything else Iýve run in my column! The answers fell into two basic approaches. One was to build a table of invalid two-letter codes and join it to the target table, as had been done in the original puzzle. The other approach was like Andreaýs ý count the number of valid state codes in the list and calculate what the char_length of the list should be if they were all valid and separated by commas. Failure means at least one bad code. Here are some other answers:

From Marko Hantula in Finland:

SELECT T1.class, T1.state_list
FROM MyTable AS T1, StateCodes AS S1
WHERE T1.state_list NOT LIKE ý%ý || S1.state || ý%ý GROUP BY T1.class, T1.state_list
HAVING COUNT(*) 
<> ((SELECT COUNT(*) FROM StateCodes) 
- ((CHAR_LENGTH(T1.state_list) + 1) / 3))

SELECT T1.class, A3.badstate 
FROM MyTable AS T1,
(SELECT A1.letter||A2.letter AS badstate
FROM Alphabet AS A1, Alphabet AS A2
EXCEPT 
SELECT state FROM StateCodes) AS A3 
WHERE POSITION(A3.badstate IN T1.state_list) > 0;

LeLuis Velo, of Bari, Italy came up with another version of the "count the char_ length()" solution. First, you create an auxiliary table of the natural numbers:

CREATE TABLE Naturals 
(nat INTEGER NOT NULL CHECK (nat > 0));
INSERT INTO Naturals VALUES ((1), (2), (3), ... (n));

Then the query will be:

SELECT class, (nat-1)*3+1 AS list_position, 
SUBSTRING(state_list FROM (nat-1)*3+1 FOR 2) 
FROM MyTable, Naturals 
WHERE SUBSTRING(state_list FROM (nat-1)*3+1 FOR 2) <> ýý;

This tells you where the error is located, which could be very useful in data scrubbing. ýJoe Celko