Re: [LUG] OT: Cats...

[kevin bailey <kev.bailey@xxxxxxxxxxx>]

hi guys,

i hate to be the saddest of all but - this might stop someone from 
losing a few hours/days.....see below...

> - -SELECT * FROM users WHERE clue > 0
> +SELECT * FROM users WHERE (clue != NULL AND clue > 0);

there is a problem with the WHERE clause "clue != NULL"...

you can not carry out this type of comparison with NULLS - because even 
if clue is set to NULL then NULL does NOT equal NULL.  so effectively 
this will always evaluate to TRUE.

using NULLS this way leads to trouble.

in T-SQL you can use a function like ISNULL().  this looks at a value 
and if it is null it will return an expression as specified - so

WHERE ISNULL(clue, '0') = '0'

will evaluate to true if clue is set to NULL.

or alternatively the SQL standard specifies IS [NOT] NULL etc.


look at this on the postgresql docs...

http://www.postgresql.org/idocs/index.php?functions.html

and this is a quote...
--------
Do not write expression = NULL because NULL is not "equal to" NULL. 
NULL represents  an unknown value, and it is not known whether two 
unknown values are equal.)
--------

this is (one) of the common mistakes we come across when replacing 
crappy access DB's with something more professional.  if anybody is 
writing DB apps i always recommend they get through a decent SQL book 
first - Instant SQL by [dunno] Celko is a good one,

kev


> ;)
>
> ~ Theo
>
>
> - -- 
>
> Theo Zourzouvillys
>  http://zozo.org.uk/
>
> Q:      How do you play religious roulette?
> A:      You stand around in a circle and blaspheme and see who gets
>         struck by lightning first.
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.0.7 (GNU/Linux)
>
> iD8DBQE9NyBE448CrwpTn6YRAi1rAKDl5VNQ+eKCp0CZRMwfoZcrGsK9IACfW4xI
> Gt5wSuxKKeilbAimSeXvqTM=
> =OHz4
> -----END PGP SIGNATURE-----
>
>
> --
> The Mailing List for the Devon & Cornwall LUG
> Mail majordomo@xxxxxxxxxxxx with "unsubscribe list" in the
> message body to unsubscribe.



--
The Mailing List for the Devon & Cornwall LUG
Mail majordomo@xxxxxxxxxxxx with "unsubscribe list" in the
message body to unsubscribe.