Board index » delphi » Using aliases with SQL server

Using aliases with SQL server


2004-10-09 01:46:25 AM
delphi218
In Sybase it is possible to have a select statement as follows
select T.*, (some condition) as Alias1, (if some condition then "true" else
Alias1) as Alias2
from Table T
As you can see one can refer to and use the value of Alias1 in the second
field. Is this possible in MS SQL Server 2000. If it is how do I do it?
Thanks
Gerhard
 
 

Re:Using aliases with SQL server

You can use a case statement.
From MS SQL Books Online:
USE pubs
GO
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price>= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
--
Alain Quesnel
XXXX@XXXXX.COM
www.logiquel.com
"Gerhard Venter" <XXXX@XXXXX.COM>writes
Quote
In Sybase it is possible to have a select statement as follows

select T.*, (some condition) as Alias1, (if some condition then "true"
else
Alias1) as Alias2
from Table T

As you can see one can refer to and use the value of Alias1 in the second
field. Is this possible in MS SQL Server 2000. If it is how do I do it?

Thanks
Gerhard


 

Re:Using aliases with SQL server

Thanks for the reply but that was not quite what I meant. Here is my real
example maybe it is a bit clearer now.
select C.*,
(case
when CITY <>'' then
(case
when STATE <>'' then
(case
when ZIP <>'' then CITY + ', ' + STATE + ' ' + ZIP else
CITY + ', ' + STATE
end)
when ZIP <>'' then CITY + ', ' + STATE + ' ' + ZIP
when ZIP <>'' then CITY + ', ' + ZIP else CITY
end)
when STATE <>'' then
(case
when ZIP <>'' then CITY + ', ' + STATE + ' ' + ZIP else CITY +
', ' + STATE
end)
when ZIP <>'' then CITY + ', ' + STATE + ' ' + ZIP
when ZIP <>'' then CITY + ', ' + ZIP
when STATE <>'' then
(case
when ZIP <>'' then STATE + ' ' + ZIP else STATE
end)
when ZIP <>'' then STATE + ' ' + ZIP
when ZIP <>'' then ZIP else null
end) as 'CITY_ST_ZIP', <<<<<<< here I alias the first
field (Alias1)
ADDR1 as ADDR_LINE1,
(case
when coalesce(ADDR2,'') <>'' then ADDR2 else CITY_ST_ZIP
<<<<< error here CITY_ST_ZIP is unknown column
end) as ADDR_LINE2,
I refer here to the alias in previous field
(case
I want to use its value
when coalesce(ADDR2,'') <>'' then CITY_ST_ZIP else null
end) as ADDR_LINE3
from
DM.COMPANY C
Thanks
Gerhard.
"Alain Quesnel" <XXXX@XXXXX.COM>writes
Quote
You can use a case statement.

From MS SQL Books Online:

USE pubs
GO
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price>= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price


--

Alain Quesnel
XXXX@XXXXX.COM

www.logiquel.com


"Gerhard Venter" <XXXX@XXXXX.COM>writes
news:4166d26f$XXXX@XXXXX.COM...
>In Sybase it is possible to have a select statement as follows
>
>select T.*, (some condition) as Alias1, (if some condition then "true"
else
>Alias1) as Alias2
>from Table T
>
>As you can see one can refer to and use the value of Alias1 in the
second
>field. Is this possible in MS SQL Server 2000. If it is how do I do it?
>
>Thanks
>Gerhard
>
>


 

Re:Using aliases with SQL server

Hi Gerhard!
On Fri, 8 Oct 2004 10:46:25 -0700, "Gerhard Venter"
<XXXX@XXXXX.COM>writes:
Quote
In Sybase it is possible to have a select statement as follows

select T.*, (some condition) as Alias1, (if some condition then "true" else
Alias1) as Alias2
from Table T

As you can see one can refer to and use the value of Alias1 in the second
field. Is this possible in MS SQL Server 2000. If it is how do I do it?
not possible.