Comparison operator in Select list

Jim Devenish

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.

Jun 22 '06 #1

Jim Devenish

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

Jun 22 '06 #2

Jim Devenish

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

Jun 22 '06 #3

Razvan Socol

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

Jun 22 '06 #4

Jim Devenish

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

Jun 22 '06 #5

Erland Sommarskog

Jim Devenish (in***************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jun 23 '06 #6