467,146 Members | 1,232 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,146 developers. It's quick & easy.

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.

Jun 22 '06 #1
  • viewed: 1745
Share:
5 Replies
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
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
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
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
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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Craig Keightley | last post: by
4 posts views Thread by Evil Bert | last post: by
37 posts views Thread by spam.noam@gmail.com | last post: by
43 posts views Thread by michael.f.ellis@gmail.com | last post: by
7 posts views Thread by matt@mailinator.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.