COMPUTE and alias

I have a query which produces a set of summaries using the compute clause.
This is what I want but all the columns in the compute result set have the
column name "sum". I have tried an alias e.g.
compute sum(cola) as cola_alias
but this throws an error. The wierd thing is all the columns have a name of
sum (they are all sums). I thought column names were supposed to be unique.
I can reference them in code by their ordinal position but it's not really
ideal. I guess I am doing something wrong but I am unsure what. Regards,
Chris."Chris" <nospam@.nospam.com> wrote in message
news:OyrOySozHHA.1208@.TK2MSFTNGP05.phx.gbl...
>I have a query which produces a set of summaries using the compute clause.
>This is what I want but all the columns in the compute result set have the
>column name "sum". I have tried an alias e.g.
> compute sum(cola) as cola_alias
> but this throws an error. The wierd thing is all the columns have a name
> of sum (they are all sums). I thought column names were supposed to be
> unique. I can reference them in code by their ordinal position but it's
> not really ideal. I guess I am doing something wrong but I am unsure what.
> Regards, Chris.
>
COMPUTE is deprecated. Use CUBE or ROLLUP instead. They are more powerful
and easier to use.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

COMPUTE and alias

I have a query which produces a set of summaries using the compute clause.
This is what I want but all the columns in the compute result set have the
column name "sum". I have tried an alias e.g.
compute sum(cola) as cola_alias
but this throws an error. The wierd thing is all the columns have a name of
sum (they are all sums). I thought column names were supposed to be unique.
I can reference them in code by their ordinal position but it's not really
ideal. I guess I am doing something wrong but I am unsure what. Regards,
Chris."Chris" <nospam@.nospam.com> wrote in message
news:OyrOySozHHA.1208@.TK2MSFTNGP05.phx.gbl...
>I have a query which produces a set of summaries using the compute clause.
>This is what I want but all the columns in the compute result set have the
>column name "sum". I have tried an alias e.g.
> compute sum(cola) as cola_alias
> but this throws an error. The wierd thing is all the columns have a name
> of sum (they are all sums). I thought column names were supposed to be
> unique. I can reference them in code by their ordinal position but it's
> not really ideal. I guess I am doing something wrong but I am unsure what.
> Regards, Chris.
>
COMPUTE is deprecated. Use CUBE or ROLLUP instead. They are more powerful
and easier to use.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sqlsql

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