>shouldn't the following view definition return only records that have
>lastname beginning with 'A' and empty(disdate)? It doesn't - it returns
>records that have lastname beginning with 'A' regardless of disdate.
>Am I missing something simple?
>
>SELECT Clients.cidnum, Clients.clastname, Clients.cfirstname,;
>Clients.cmiddlename, Clients.cadmdate, Clients.disdate;
>FROM cms!clients;
>WHERE Clients.cidnum LIKE ?vp_idnum;
>OR (UPPER(Clients.clastname) LIKE UPPER(?vp_last));
>OR (UPPER(Clients.cfirstname) LIKE UPPER(?vp_first));
>OR (UPPER(Clients.cmiddlename) LIKE UPPER(?vp_middle);
>AND EMPTY(Clients.disdate) = ?vp_active)
I guess you should put all the ...Like ... OR ... Like into parentheses.
Since AND has higher syntactical priority, you're actually getting
Like1 or like2 or like3 or (like4 and empty())
You probably wanted something like
(Like1 or like2 or like3 or like4) and empty()