>> From: Jochem van Dieten
>>
>> WHERE prov = TRUE.
>> WHERE NOT prov IS NULL OR prov = FALSE
>
> SQL Server 2000 doesn't understand TRUE or FALSE - it thinks they
> are a column reference - booleans are 1 or 0
http://register.microsoft.com/mswish/suggestion.asp
>> This specific case is not a matter of being too used to
>> Access' tricks, this is a matter of MS SQL Server not being
>> standard compliant. While the code is not exactly an example
>> of 'defensive' SQL written with portability in mind, MS SQL
>> Server should handle this better. After all, according to
>> your classification MS SQL Server is a REAL server level
>> database engine, so it is not unreasonable to expect it to
>> behave better then his desktop nephew :-)
>
> The same goes for Oracle though, there are certain oddities about
> Oracle which are non-standard (like being able to update an Identity
> column) which you have to learn to use it properly
Every database has oddities. However, the example you are giving is
not an oddity, it is correct behaviour according to the SQL standard.
> Same goes for any of the "big" database engines, there are things
> which do and do not work (I don't think mySQL can do sub-queries)
I don't think MySQL qualifies as a "big" database engine anyway :-)
> There is no real direct portability apart from the simplest of SQL
> statements
True.
But the "simplest of SQL statements" is a moving target. A few years
ago an outer join was a complex SQL statement that needed to be coded
using dbms specific syntax. Nowadays we consider it a simple one and
we can use ISO syntax with nearly all databases.
If sufficient people complain to Microsoft that it is easier to
migrate from Access to PostgreSQL as from Access to MS SQL Server
because of the lack of BOOLEANs, who knows what we might consider
simple next year.
Jochem
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
