Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!swrinde!elroy.jpl.nasa.gov!turnkey!orchard.la.locus.com!prodnet.la.locus.com!jfr
From: jfr@locus.com (Jon Rosen)
Newsgroups: comp.databases
Subject: Re: Bizarre Database Reporting
Keywords: Null Values, Reports
Message-ID: <24354@oolong.la.locus.com>
Date: 14 May 91 18:13:14 GMT
References: <1991May13.162936.14729@hellgate.utah.edu>
Organization: Locus Computing Corp, Los Angeles
Lines: 79

In article <1991May13.162936.14729@hellgate.utah.edu> knechod%peruvian.utah.edu@cs.utah.edu (Kevin Nechodom) writes:
>Here is a question that I have used to harass the database companies
>that bring in their "Wonderful World of Our Database" shows.  This is just
>a base case; any response to this question will quite likely inspire a flurry
>of counter-questions.  Anyway...
>
>Imagine that you have a single table of many columns (>500) and many rows
>(>5000).  Let's allow _any_ of those rows to be null (with the exception of
>the key variable).
>
>I would like to write a report that returns all cases where the data is
>missing.  Since I am allowing for many columns, it can't be simply a columnar
>report.  I envision this report to look like:
>
>    ---------------------------
>    | Key Value | Column Name |
>    |-------------------------|
>    | Key 1     | Field 1     |
>    | Key 1     | Field 2     |
>    | ...       | ...         |
>    | Key 1     | Field xxx   |
>    | Key 2     | Field nn    |
>    | ...       | ...         |
>    ---------------------------
>
>Is there _anything_ in the database universe that does something like this?
>Where is it?  Who does it? etc.  
>

To directly answer your question, the answer, to the best of my knowledge,
is No, Nowhere, No-one :-)
 
However, there are several solutions (none of them optimal of course, but
can be used in interesting situations :-)
 
First of all, the obvious solution here if you have a relational DBMS
like DB2 or Ingres with an embedded SQL capability, is to just write
a program to do it.  The program would be pretty trivial (albeit long)
in that it would look something like (Pascal psuedo-code here, thank you,
please accept my apologies to all the C bigots :-):  
 

  exec sql declare cursor csr as select key,a,b,c,... from tbl where ...; 
  exec sql open csr;
  repeat
    exec sql fetch csr into :key,:a:anull,:b:bnull,:c:cnull,...;
    if sqlcode = 0 then begin
      if anull = -1 then writeln(key,"Field A is null");
      if bnull = -1 then writeln(key,"Field B is null");
      if cnull = -1 then writeln(key,"Field C is null");
        ...
    end;
  until sqlcode <> 0;
  exec sql close csr;  
 
If the embedded SQL supports dynamic SQL, this can even be done with
a general purpose routine that doesn't know what columns are in the
table until execution time.  THe routine becomes more complex but
it can use a DESCRIBE to find out what the attribute columns are,
and have a general loop that cycles through all the null column
data for each row retrieved.  I leave this as a exercise for the
reader :-)
 
There is also a kludgy SQL solution for small tables (I know your
question asked about large column tables but I can't do that one).
In any case, you can do the following:
 
   SELECT KEY,"FIELD A IS NULL" FROM TBL WHERE A IS NULL 
   UNION
   SELECT KEY,"FIELD B IS NULL" FROM TBL WHERE B IS NULL 
   UNION
   SELECT KEY,"FIELD C IS NULL" FROM TBL WHERE C IS NULL
    ... (ad nauseum until your system croaks from too many unions)
   ORDER BY 1,2
 
I know, I know, this is gross and disgusting... Don't blame me,
I didn't invent SQL (Simplistic Query Language :-)...
 
Jon Rosen