EXCEPT doesn't work well in PostGres. Try replacing EXCEPT with NOT IN (...)
At 06:00 PM 9/7/00, chris mutchler wrote:
>I have a perl script that interacts with my postgresql server through the
>DBI module. When, I send my query to the server it will work fine with
>the following:
>
>$sth = $dbh->prepare("
> SELECT p.ids_name, m.start_time, m.end_time
> FROM mail_schedule m, personnel p
> WHERE p.ids_int = m.ids_int AND
> m.dow = $weekday
> UNION
> SELECT p.ids_name, c.on_start, c.on_end
> FROM mail_changes c, personnel p
> WHERE p.ids_int = c.ids_int
>");
>
>But as soon as I place the EXCEPT call in there it stops working and
>doesn't return any values:
>
>$sth = $dbh->prepare("
> SELECT p.ids_name, m.start_time, m.end_time
> FROM mail_schedule m, personnel p
> WHERE p.ids_int = m.ids_int AND
> m.dow = $weekday
> UNION
> SELECT p.ids_name, c.on_start, c.on_end
> FROM mail_changes c, personnel p
> WHERE p.ids_int = c.ids_int
> EXCEPT
> SELECT p.ids_name, c.on_start, c.on_end
> FROM mail_changes c, personnel p
> WHERE p.ids_int = c.ids_int
> AND c.on_start = 0
> AND c.on_end = 0
>");
>
>Does anyone have any suggestions as to why my EXCEPT call isn't working?
>If you see some drastic or subtle syntax error let me know. I have never
>used EXCEPT before, and am not having much luck. thanks.
>
>Chris Mutchler
>davron@leibnizcreations.com