[sqlite] Found the problem: Re: [sqlite] Is this a valid syntax
I found the problem and have a workaround now. The problem is caused by a selection field, which is a text field, that might be null. This field can either be - null - empty string (don't know if this is different from null) -'0' - 0 (don't know if this is different from the string '0') I'ld consider this as a bug, but I'm not sure who's to blame ;-), SQLite or the Delphi wrapper or my own program I'll study the datatype page again: http://www.sqlite.org/datatype3.html Thank you all for your suggestions, which finally solved my problem, cheers, Stef Mientki Kamer van Koophandel - handelsregister 41055629 / Netherlands Chamber of Commerce - trade register 41055629 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this a valid syntax
Dennis Cote <[EMAIL PROTECTED]> wrote: > > Being a C programmer, Richard extended SQLite to allow C syntax for > equality and inequality comparisons as shown at > http://www.sqlite.org/lang_expr.html even though it is non standard. Actually, the reason I did this was because PostgreSQL did it before me and I used the PostgreSQL documentation as a guide while developing SQLite. :-) -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this a valid syntax
Dennis Cote wrote: Stef Mientki wrote: But it doesn't solve my problem :-( I've the feeling that despite the suggestions of Igor, the problem still exists, caused by the zero values ?? I'll try tomorrow again with some other values. Stef, Oh... I though Igor had solved your problem so I didn't give it much thought. My reading of your query would suggest that you should try this: SELECT PO.* FROM Koppel LEFT JOIN PO ON (Koppel.K_App == PO.App) WHERE (Koppel.K_naam == 'MVE') AND (PO.ALL_answered == '0') The last two conditions should select the result rows and not affect the join operation. Only the comparisons of the fields from both tables should affect the join. Thanks Dennis, but I tried that already :-( Tomorrow is a new day, with new possibilities. cheers, Stef HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this a valid syntax
Stef Mientki wrote: But it doesn't solve my problem :-( I've the feeling that despite the suggestions of Igor, the problem still exists, caused by the zero values ?? I'll try tomorrow again with some other values. Stef, Oh... I though Igor had solved your problem so I didn't give it much thought. My reading of your query would suggest that you should try this: SELECT PO.* FROM Koppel LEFT JOIN PO ON (Koppel.K_App == PO.App) WHERE (Koppel.K_naam == 'MVE') AND (PO.ALL_answered == '0') The last two conditions should select the result rows and not affect the join operation. Only the comparisons of the fields from both tables should affect the join. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this a valid syntax
Dennis Cote wrote: Stef Mientki wrote: I don't know if this is the problem, but, for some reason you're mixing C/C++ syntax in with SQL there. You don't use '==', you should just use '=' You don't use '!=', you should use '<>' thanks Paul, but although I can never find this information when I need it :-( AFAIK, both notations are allowed. Besides that I tried both and it doesn't change the situation. hi Dennis, Paul and Stef, Being a C programmer, Richard extended SQLite to allow C syntax for equality and inequality comparisons as shown at http://www.sqlite.org/lang_expr.html Aha, that was the page I was looking for !! even though it is non standard. If you want your SQL code to be portable to other database engines you should use the standard syntax that Paul suggested. I agree, thanks. But it doesn't solve my problem :-( I've the feeling that despite the suggestions of Igor, the problem still exists, caused by the zero values ?? I'll try tomorrow again with some other values. cheers, Stef HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this a valid syntax
Stef Mientki wrote: I don't know if this is the problem, but, for some reason you're mixing C/C++ syntax in with SQL there. You don't use '==', you should just use '=' You don't use '!=', you should use '<>' thanks Paul, but although I can never find this information when I need it :-( AFAIK, both notations are allowed. Besides that I tried both and it doesn't change the situation. Paul and Stef, Being a C programmer, Richard extended SQLite to allow C syntax for equality and inequality comparisons as shown at http://www.sqlite.org/lang_expr.html even though it is non standard. If you want your SQL code to be portable to other database engines you should use the standard syntax that Paul suggested. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this a valid syntax
I don't know if this is the problem, but, for some reason you're mixing C/C++ syntax in with SQL there. You don't use '==', you should just use '=' You don't use '!=', you should use '<>' thanks Paul, but although I can never find this information when I need it :-( AFAIK, both notations are allowed. Besides that I tried both and it doesn't change the situation. cheers, Stef - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this a valid syntax
At 16:45 17/04/2007, Stef Mientki wrote: I don't understand this behaviour, is this too complex ? or am I doing something wrong ? I use the following syntax, and I get 7 records back, (which is not correct in my opinion) SELECT PO.* FROM Koppel LEFT JOIN PO WHERE (Koppel.K_App == PO.App) AND (Koppel.K_naam == 'MVE') AND (PO.ALL_answered == '0') SELECT PO.* FROM Koppel LEFT JOIN PO WHERE (Koppel.K_App == PO.App) AND (Koppel.K_naam == 'MVE') AND (PO.ALL_answered != '0') I don't know if this is the problem, but, for some reason you're mixing C/C++ syntax in with SQL there. You don't use '==', you should just use '=' You don't use '!=', you should use '<>' So, try SELECT PO.* FROM Koppel LEFT JOIN PO WHERE (Koppel.K_App = PO.App) AND (Koppel.K_naam = 'MVE') AND (PO.ALL_answered <> '0') PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/ - To unsubscribe, send email to [EMAIL PROTECTED] -
