Board index » delphi » single garbage record in SELECT query

single garbage record in SELECT query

hi all,
i'm writing some integrity checks on my tables.  unfortunately, if everything
is ok (ie. no errors found) this query always returns with one record where
Class1_KEY is blank (null or empty, i don't know, but blank if you look at it
in the Database Explorer), and Class2_KEY, Class3_KEY as -1, and the error
message in Failure_Reason.  the RxClass2 table is fine (it's small enough that
i can visually inspect it).

what's the deal?  how can i prevent the one useless record showing up.  the
count, incidentally, is 0.  i've never encountered this before as i usually
have other criteria before the exists statement (e.g. Some_Field = 1 AND NOT
EXISTS (...) ).

thanks,
dave

the query:

SELECT   Class1_KEY
       , '-1' AS Class2_KEY
       , '-1' AS Class3_KEY
       , 'Invalid foreign key in RxClass2. (' || CAST( COUNT(*) AS CHAR ) ||
')' AS Failure_Reason

FROM     RxClass2 C2

WHERE    NOT EXISTS (
           SELECT *
           FROM   RxClass1 C1
           WHERE  C2.Class1_KEY = C1.Class1_KEY
         )

GROUP BY Class1_KEY

 

Re:single garbage record in SELECT query


just to make things more clear, this "garbage" record does not appear if the query
finds records matching the criteria.

thanks,
dave

Re:single garbage record in SELECT query


ok, further info...
i guess the _real_ reason didn't see this problem before is that i wasn't using the
COUNT function.  if i take it out then it works ok, and i don't get the garbage
record.  i don't suppose there's anyway to have COUNT in there an not get the
"garbage" record when COUNT = 0?

thanks,
dave

Re:single garbage record in SELECT query


David Beardwood <dsbea...@mcmaster.ca> wrote in article
<360FBC0F.4FBE6...@mcmaster.ca>...

Quote
> ok, further info...
> i guess the _real_ reason didn't see this problem before is that i wasn't
using the
> COUNT function.  if i take it out then it works ok, and i don't get the
garbage
> record.  i don't suppose there's anyway to have COUNT in there an not get
the
> "garbage" record when COUNT = 0?

Try this.
                -- Kirk

 SELECT  
        Class1_KEY,
            '-1' AS Class2_KEY,
            '-1' AS Class3_KEY,
        COUNT(*) as TOT,
           'Invalid foreign key in RxClass2. (' || CAST( TOT AS CHAR ) ||
')' AS Failure_Reason

 FROM     RxClass2 C2
 WHERE    
        TOT > 0 AND
NOT EXISTS (
            SELECT *
            FROM   RxClass1 C1
            WHERE  C2.Class1_KEY = C1.Class1_KEY
          )
GROUP BY Class1_KEY

Re:single garbage record in SELECT query


On Mon, 28 Sep 1998 10:23:44 -0400, David Beardwood <dsbea...@mcmaster.ca>
wrote:

Quote
>i'm writing some integrity checks on my tables.  unfortunately, if everything
>is ok (ie. no errors found) this query always returns with one record where
>Class1_KEY is blank (null or empty, i don't know, but blank if you look at it
>in the Database Explorer), and Class2_KEY, Class3_KEY as -1, and the error
>message in Failure_Reason.  the RxClass2 table is fine (it's small enough that
>i can visually inspect it).

>what's the deal?  how can i prevent the one useless record showing up.  the
>count, incidentally, is 0.  i've never encountered this before as i usually
>have other criteria before the exists statement (e.g. Some_Field = 1 AND NOT
>EXISTS (...) ).

This is the way SQL was designed to work. When you have a result set that
returns or operates on no rows, the SUM function will return a NULL value
and COUNT a zero. In fact, all aggregate functions except COUNT return a
NULL under these circumstances: SUM, MIN, MAX, AVG.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                 "What is success in this world? I would say
Technical Publications         it consists of four simple things: to live
INPRISE Corporation            a lot, to love a lot, to laugh a lot, and
http://www.inprise.com/delphi  from it all, to learn a lot."
                                                     -- Richard J. Needham

Other Threads