473,221 Members | 1,955 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,221 software developers and data experts.

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
5 1900
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Craig Keightley | last post by:
is it possible to compare acomma separated list aginst another eg comma list 1 => 1,2,3,4,5 comma list 2 => 3,5 can you check that 3 is in both, and 5 is in both, therfore they match??? the...
0
by: Vlad | last post by:
I operate with 2 tables: tbLocations and tbDepartments. Both have a column named Town. tbLocations.Town looks like long unique list of different towns world wide: Copenhagen Madrid Roma
4
by: Evil Bert | last post by:
Is there anyway to compare a field with multiple string values using LIKE? Here's the statement I have now: SELECT * FROM list WHERE email LIKE CONVERT( _utf8 'hotmail@hotmail.com' USING latin1...
2
by: Matthew Clement | last post by:
I'm currently building a form (called frmReports) to set the criteria for a query, but I'm having some trouble with syntax and hope that one of the guru's here can help me achieve what I'm do. ...
37
by: spam.noam | last post by:
Hello, Guido has decided, in python-dev, that in Py3K the id-based order comparisons will be dropped. This means that, for example, "{} < " will raise a TypeError instead of the current...
43
by: michael.f.ellis | last post by:
The following script puzzles me. It creates two nested lists that compare identically. After identical element assignments, the lists are different. In one case, a single element is replaced. In...
7
by: matt | last post by:
hello, i have been given a challenging project at my org. i work on an inventory management web application -- keeping tracking of parts assigned to projects. in the past, i built an in-house...
0
by: SvenMathijssen | last post by:
Hi, I've been wrestling with a problem for some time that ought to be fairly simple, but turns out to be very difficult for me to solve. Maybe someone here knows the answer. What I try to do is...
2
by: apollock | last post by:
Our application is having an issue where a query in a stored procedure periodically gets a bad plan with a Lazy Spool Operator that changes the query execution time from 5ms to 900ms and kills our...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.