>> From: Claude Schneegans
>>
>> Sure, this is standard SQL: in a SELECT <Value
>> _expression_>,... a value _expression_ can be a column name, a
>> function or any combination in an _expression_. If the
>> _expression_ is boolean, the column should contain true, false
>> or null. This works in Access, not in MS SQL ?
>
> I don't think you can do evaluations like that in standard ANSI SQL
> - you'd have to run a function on it
I don't know about ANSI SQL (it is so old I can't find any specs
anymore), but according to ISO 9075:2003 the x < y form is a boolean
value _expression_ (comparison predicate) that should work. But I would
recommend adding some AS fieldname to it.
>> Also, I've note been able to use the column "prov" directly
>> as a boolean in the WHERE _expression_: WHERE prov works in
>> Access but not with MS SQL.
>From glancing through the standard it is not immediately clear to me
whether that should work or not.
> The problem with short-cutting like this is that a boolean field has
> 3 values:
> 1, 0 and NULL
It has the values TRUE, FALSE and UNKNOWN/NULL. The values 0 and 1 are
workarounds in databases that do not have proper booleans.
> If you want to just get 1 (as being "true") then you would have to
> to do:
> WHERE prov = 1
WHERE prov = TRUE.
> And in the opposite, because NULL is a different "state", you can't
> treat it like 1 or 0, so you have to do:
> WHERE prov is NULL or prov = 0
WHERE NOT prov IS NULL OR prov = FALSE
> If you're too used to using one database's tricks, then you will
> always have problems converting to a different engine - you have to
> remember that Access is not a REAL database engine either - it is a
> desktop tool, so it will have features to make a desktop app helpful
> (and be compatible with the other Office apps), but most of these
> won't be in a server level database engine (such as SQL Server,
> Oracle, mySQL, Protégé, etc.)
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 :-)
Jochem
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
