Problem with parser, perhaps a bug?
Server version: 5.0.18 (command-line client also).
OS: Win 2003 Server Std.
I am really struggelig with a query, but have managed to succeed with a subset of there original query here:
SELECT distinct personer.persnavn, f.navn, f.adr1, f.adr2, poststeder.postnr, poststeder.poststed,
personer.ikkesend, personer.dirmail, poststeder.kommunenr, f.telefax, land.landkode, f.kontakttype, f.firmaid, pi.demo, pi.produktid
FROM
land INNER JOIN
poststeder ON (land.landkode = poststeder.landkode) INNER JOIN
firmaer f ON (poststeder.landkode = f.landkode) AND (poststeder.postnr = f.postnr) INNER JOIN
personer ON (f.firmaid = personer.firmaid) INNER JOIN
produktinnstallasjoner pi ON (f.firmaid = pi.firmaid)
WHERE
((personer.ikkesend=0) OR (personer.ikkesend Is Null)) AND
((f.opphort Is Null) OR (f.opphort=0)) AND
((pi.demo is null) OR (pi.demo = 0)) AND
((f.kontakttype Is Null) Or (f.kontakttype='')) AND
land.landkode = 'NO' AND
((pi.oppsagt is null) OR (pi.oppsagt = 0))
order by f.navn, personer.persnavn limit 100;
Just by rearranging the lines in the where clause I get a parse error:
WHERE
((personer.ikkesend=0) OR (personer.ikkesend Is Null)) AND
((f.opphort Is Null) OR (f.opphort=0)) AND
((pi.demo is null) OR (pi.demo = 0)) AND
((pi.oppsagt is null) OR (pi.oppsagt = 0))
((f.kontakttype Is Null) Or (f.kontakttype='')) AND
land.landkode = 'NO' AND
I only moved the "((pi.oppsagt is null) OR (pi.oppsagt = 0))" up a few lines. Also, if I add more clauses to the where clause it fails. The "limit 100" is just there to avoid listing all rows in the rare cases that it works.
The error message I when using the second where clause is:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use n
ear '((f.kontakttype Is Null) Or (f.kontakttype='')) AND
land.landkode = 'NO' AND
o' at line 14
Can somebody please help me understand why the order of the clauses in the where clause matters? Or is this simply a bug (I would think it is...).
Thanks in advance!
Trygve