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

Gustav,

You could always use a case statement:

Order By (case when [SortOrder] = 0 then 1 else 0 end)

I'm not sure how efficient it would be but I've used it for more complicated sorts.

Hope that helps.

James Barash

-----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 5: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