Sunday, February 12, 2012

Comparison operator in Select list

I want to create a column alias to represent the comparison of two
columns (ie a boolean result of True or False). A simple example is:
Select VehicleFinanceID, SalePrice > PurchasePrice As isProfit
>From VehicleFinance

but I get an error 'Incorrect syntax near >'

Books online states that the select_list can contain column_name or
expression

An expression is a column name, constant, function, any combination of
column names, constants and functions connected by an operator

> is a binary operator

So why do I get this error.

Incidentally, if I use an arithmetic operator such as +, there is no
problem.A gremlin put the erroneous > in front of From :-(

The Select statement was intended to be:

Select VehicleFinanceID, SalePrice > PurchasePrice As isProfit
>From VehicleFinance

Jim|||There it is again. I can't win on this one. Please disregard the > in
front of From

Jim

Jim Devenish wrote:
> A gremlin put the erroneous > in front of From :-(
> The Select statement was intended to be:
> Select VehicleFinanceID, SalePrice > PurchasePrice As isProfit
> >From VehicleFinance
>
> Jim|||Hello, Jim

Try something like this:
SELECT VehicleFinanceID,
CASE WHEN SalePrice > PurchasePrice THEN 1 ELSE 0 END As isProfit
FROM VehicleFinance

SQL Server does not support a boolean data type, so you must use some
convention to represent boolean values (for example: 1=True, 0=False or
'Yes'=True, 'No'=False, etc).

A boolean expression can only be used in an IF statement, a WHILE
statement, a CASE WHEN expression, a WHERE/HAVING clause, etc.

Razvan|||Thanks Razvan. That is what I had done but it seemed cumbersone for
something that is essentially simple.

At least I know what I can and cannot do, now.

Jim

Razvan Socol wrote:
> Hello, Jim
> Try something like this:
> SELECT VehicleFinanceID,
> CASE WHEN SalePrice > PurchasePrice THEN 1 ELSE 0 END As isProfit
> FROM VehicleFinance
> SQL Server does not support a boolean data type, so you must use some
> convention to represent boolean values (for example: 1=True, 0=False or
> 'Yes'=True, 'No'=False, etc).
> A boolean expression can only be used in an IF statement, a WHILE
> statement, a CASE WHEN expression, a WHERE/HAVING clause, etc.
> Razvan|||Jim Devenish (internet.shopping@.foobox.com) writes:
> There it is again. I can't win on this one. Please disregard the > in
> front of From

The reason this happens is because of the mbox format used by old
Unix mailers. This format is also used by some newsreaders for archives
I believe. In this format, a new message always starts with "From ".
Since a preceding message always ends with two or three newlines,
there is some safety precaution in the format, but not waterproof.

For this reason, Unix mailers that uses this format adds a > before
"From" when it appears first on a line. I've noticed that Google news,
that I see that you are using, also does this to be, I guess, a good
net citizen.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

No comments:

Post a Comment