BorlandTalk.com

Gerhard Venter · Guest

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

Alain Quesnel · Guest

You can use a case statement.

From MSSQL 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
[email]alainsansspam (AT) logiquel (DOT) com[/email]

www.logiquel.com

"Gerhard Venter"

Gerhard Venter · Guest

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" <alainsansspam (AT) logiquel (DOT) com> wrote

Tomislav Kardaš · Guest

Hi Gerhard!

On Fri, 8 Oct 2004 10:46:25 -0700, "Gerhard Venter"
<gerhard.venter (AT) comtronic (DOT) com> wrote: