AFAIK NULL is not a value according to SQL spec, so it doesn't match in a "not
in" clause (or any other value comparing clause for that matter, i.e. blabla
>= 10 will not match rows where blabla is null). Therefor I'd say the result
of 30 is correct.
If you want to see null results too you should say so, i.e.
CON.IS_SUBSCRIBED NOT IN ('X', 'P') OR CON.IS_SUBSCRIBED IS NULL
On Saturday 18 February 2006 21:51, Brendan Duddridge wrote:
> Hi,
>
> I have a query that returns 569 rows in FrontBase, but only 30 rows
> in Postgres. The data is the same as I just finished copying my
> entire database over from FrontBase to Postgres.
>
> I've reduced my problem to the following statement and have
> discovered that FrontBase returns null rows along with the rows that
> match the query and PostgreSQL only returns the not null rows.
>
> CON.IS_SUBSCRIBED NOT IN ('X', 'P')
>
> Is that normal? I guess I have to rewrite my queries to handle this
> situation.
>
> Does anyone have any idea why the two database engines might differ
> in this way?
>
> Thanks,
>
> ____________________________________________________________________
> Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com
>
> ClickSpace Interactive Inc.
> Suite L100, 239 - 10th Ave. SE
> Calgary, AB T2G 0V9
>
> http://www.clickspace.com
--
UC
--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax: +1 707 568 6416