Column Aliases

I want to refere to an aliased column in an expression in another column. I
have one called get getforename and I want to test if it is null and display
a different value but all's I get is
Invalid column name 'getforerank'. Regards, Chris.
SELECT
dbo.tblCemeteries.CemeteryName,
Surname,
age,
dod,
Forename,
CASE WHEN tblNames.Forename='liz' THEN 0
else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Forename))
END as getforerank,
case when getforerank is null then 1000
else getforerank
end,
CASE WHEN tblNames.Surname='smith' THEN 0
else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Surname))
END as getrank FROM dbo.tblNames INNER JOIN dbo.tblCemeteries
ON dbo.tblNames.Cemeteryid = dbo.tblCemeteries.CemeteryID
WHERE (KeyNameGroupID = 1) and (dateadd(year,(age-(age*2)),dod )
between '10/10/1753' and '12/06/2004') and (dod between '10/10/1753' and
'12/06/2004')
order by getrank,forename
You simply can not use aliases in the manner in which you are trying.
"Chris Kennedy" wrote:

> I want to refere to an aliased column in an expression in another column. I
> have one called get getforename and I want to test if it is null and display
> a different value but all's I get is
> Invalid column name 'getforerank'. Regards, Chris.
> SELECT
> dbo.tblCemeteries.CemeteryName,
> Surname,
> age,
> dod,
> Forename,
> CASE WHEN tblNames.Forename='liz' THEN 0
> else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Forename))
> END as getforerank,
> case when getforerank is null then 1000
> else getforerank
> end,
> CASE WHEN tblNames.Surname='smith' THEN 0
> else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Surname))
> END as getrank FROM dbo.tblNames INNER JOIN dbo.tblCemeteries
> ON dbo.tblNames.Cemeteryid = dbo.tblCemeteries.CemeteryID
> WHERE (KeyNameGroupID = 1) and (dateadd(year,(age-(age*2)),dod )
> between '10/10/1753' and '12/06/2004') and (dod between '10/10/1753' and
> '12/06/2004')
> order by getrank,forename
>
>
|||Chris,
The SELECT list isn't materialized column-by-column from left to
right, so you can't use a name that's newly created in the select list
from elsewhere within the select list. You have two choices - either
replace getforerank with its definition, or use a nested derived table.
Since getforerank is messy, the derived table is probably an easier
solution. I've replaced your case statement by COALESCE, which should
be equivalent to what you have and less to type.
select
blah,
coalesce(getforerank, 1000),
blah
from (
-- your query except for the column using getforerank
select ...
this,
that,
messyexpression as getforerank
-- do not put coalesce(getforerank,1000) in this select
from ...
) D
Steve Kass
Drew University
Chris Kennedy wrote:

>I want to refere to an aliased column in an expression in another column. I
>have one called get getforename and I want to test if it is null and display
>a different value but all's I get is
>Invalid column name 'getforerank'. Regards, Chris.
>SELECT
>dbo.tblCemeteries.CemeteryName,
>Surname,
>age,
>dod,
>Forename,
>CASE WHEN tblNames.Forename='liz' THEN 0
>else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Forename))
>END as getforerank,
>case when getforerank is null then 1000
>else getforerank
>end,
>CASE WHEN tblNames.Surname='smith' THEN 0
>else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Surname))
>END as getrank FROM dbo.tblNames INNER JOIN dbo.tblCemeteries
>ON dbo.tblNames.Cemeteryid = dbo.tblCemeteries.CemeteryID
>WHERE (KeyNameGroupID = 1) and (dateadd(year,(age-(age*2)),dod )
>between '10/10/1753' and '12/06/2004') and (dod between '10/10/1753' and
>'12/06/2004')
>order by getrank,forename
>
>