[dba-SQLServer] Cannot sort on Abs([Field]=0)

Gustav:

Off the top, wouldn't something like:

Not([SomeNumericField]=0) work, as that should flip true or false result?
(That works in the Informix SQL, I am current working but am not sure
whether it would work in MS SQL.)

Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Gustav
Brock
Sent: Friday, August 17, 2012 2:05 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Cannot sort on Abs([Field]=0)

Hi Stuart and Jim

That's a very good explanation Stuart.

And Jim, no I would prefer to run SQL understood by SQL Server, and ABS() is
such a function.
But what are my alternatives? 

How would you rewrite Abs([SomeNumericField]=0)?

/gustav


>>> jlawrenc1 at shaw.ca 17-08-12 5:39 >>>
Of course people connecting to a MS SQL or real SQL server would never do
the computing in the MS Access FE...would they??

Jim 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Thursday, August 16, 2012 3:39 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Cannot sort on Abs([Field]=0)

<quote>
The result of a comparison operator has the Boolean data type. This has
three values: TRUE, FALSE, and UNKNOWN. Expressions that return a Boolean
data type are known as Boolean expressions.

Unlike other SQL Server data types, a Boolean data type cannot be specified
as the data type of a table column or variable, and cannot be returned in a
result set.
</quote>

Note the last sentence.  IOW, there are very limited places in SQL Server
queries that you can actually use "x = y" in an expression.

By using a VBA expression such as CBOOL() which is not valid SQL, you are
forcing Access to process the query locally on raw data returned by SQL
Server.  Without such an expression Access passes the actual comparison
string to SQL Server resulting in the error.

 If you play around with "SortOrder = 1" in a query window in SSMS, you will
find all sorts of similar problems.  

-- 
Stuart

On 16 Aug 2012 at 16:39, Gustav Brock wrote:

> Hi all
> 
> This works in Access SQL:
> 
>   ORDER BY Abs([SortOrder]=0)
> 
> but if the tables are linked via ODBC to SQL Server 2008, it fails:
> 
>   Incorrect syntax near '='. (#102)
> 
> I have to rewrite it like this:
> 
>   ORDER BY Abs(CBool([SortOrder]=0))
> 
> Why is that? [SortOrder] is a short integer with no Nulls.
> 
> /gustav

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com