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
>
>

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,forenameYou 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
>
>

Column Alias Behavior

I don't know why I never noticed this before, but in the code below, why can
I ORDER BY an aliased column, but I have to use the *actual* column name or
expression in the GROUP BY?
I would really rather say "GROUP BY Column1, Column2"
CREATE TABLE msl_T1 (
col1 INT,
col2 INT,
col3 INT
)
GO
SELECT col1 AS Column1, col2 * 2 AS Column2, MIN(col3) AS Column3
FROM msl_T1
GROUP BY col1, col2 * 2 -- RIGHT HERE!!
ORDER BY Column3
DROP TABLE msl_T1
GO
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneYou can use column aliases in the ORDER BY, but nowhere else. You can use a
derived table to circumvent this:
SELECT Column1, Column2, min (col3) Column3
FROM
(
SELECT col1 AS Column1, col2 * 2 AS Column2, col3
FROM msl_T1
) X
GROUP BY Column1, Column2
ORDER BY Column3
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eMlNTrE9FHA.4084@.TK2MSFTNGP10.phx.gbl...
>I don't know why I never noticed this before, but in the code below, why
>can I ORDER BY an aliased column, but I have to use the *actual* column
>name or expression in the GROUP BY?
> I would really rather say "GROUP BY Column1, Column2"
> CREATE TABLE msl_T1 (
> col1 INT,
> col2 INT,
> col3 INT
> )
> GO
> SELECT col1 AS Column1, col2 * 2 AS Column2, MIN(col3) AS Column3
> FROM msl_T1
> GROUP BY col1, col2 * 2 -- RIGHT HERE!!
> ORDER BY Column3
> DROP TABLE msl_T1
> GO
>
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> You can use column aliases in the ORDER BY, but nowhere else.
HMPH!! So much for "consistency". As Bill the Cat so eloquently put it,
"PTHPTHPTPTH" :-)
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||It's not a question of consistency.
5aa6fc669a8" target="_blank">http://groups.google.ca/group/comp.../>
5aa6fc669a8
and read Joe Celko's explanation on Select.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eWwqf5E9FHA.2676@.TK2MSFTNGP15.phx.gbl...
> HMPH!! So much for "consistency". As Bill the Cat so eloquently put it,
> "PTHPTHPTPTH" :-)
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>