Column aliases in MDX

Is it possible to use column aliases in MDX query?
I execute MDX using MSOLAP and I'd like to have a specific names for the columns returned.
Linkedserver and OPENROWSET is a one solution, but is there a way to specify column aliases in MDX query?

You can use calculated members to change names of dimension members (including measures).

So you could write your query as something like the following:

WITH MEMBER Measures.[NiceName1] as Measures.M1, Measures.[NiceName2] as M2

SELECT {Measures.[NiceName1], Measures.[NiceName2]} on 0

FROM [MyCube]

|||Using CM for that purposes is a very dangerous way.|||Thank you.
When running some examples, I'm still getting errors:
1. "Parser: The syntax for '.' is incorrect." for:
WITH MEMBER Measures.[Reseller Sales Amount] as Measures.M1, Measures.[Calendar Year].[CY 2004] as M2
SELECT
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].[CY 2004]) ON 0
FROM [Adventure Works]

2. "The Reseller Sales Amount calculated member cannot be created because a member with the same name already exists." for:
WITH MEMBER Measures.[Reseller Sales Amount] as M2
SELECT
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].[CY 2004]) ON 0
FROM [Adventure Works]

You're saying: "Using CM for that purposes is a very dangerous way."
What would be the best? Linkedserver and OPENROWSET only?

|||

Try reversing the syntax. (In other words NiceName1 was the new calculated member serving as an alias to M1 in my original example.)

You will lose the ability to do drillthrough on the calc members. Vladamir can comment on other concerns he may have about this approache.

|||

Vladimir -

Why is this considered a bad practice? What other ways are there to accomplish this? You have me very curious...

Thanks,

John

Column aliases in MDX

Is it possible to use column aliases in MDX query?
I execute MDX using MSOLAP and I'd like to have a specific names for the columns returned.
Linkedserver and OPENROWSET is a one solution, but is there a way to specify column aliases in MDX query?

You can use calculated members to change names of dimension members (including measures).

So you could write your query as something like the following:

WITH MEMBER Measures.[NiceName1] as Measures.M1, Measures.[NiceName2] as M2

SELECT {Measures.[NiceName1], Measures.[NiceName2]} on 0

FROM [MyCube]

|||Using CM for that purposes is a very dangerous way.|||Thank you.
When running some examples, I'm still getting errors:
1. "Parser: The syntax for '.' is incorrect." for:
WITH MEMBER Measures.[Reseller Sales Amount] as Measures.M1, Measures.[Calendar Year].[CY 2004] as M2
SELECT
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].[CY 2004]) ON 0
FROM [Adventure Works]

2. "The Reseller Sales Amount calculated member cannot be created because a member with the same name already exists." for:
WITH MEMBER Measures.[Reseller Sales Amount] as M2
SELECT
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].[CY 2004]) ON 0
FROM [Adventure Works]

You're saying: "Using CM for that purposes is a very dangerous way."
What would be the best? Linkedserver and OPENROWSET only?

|||

Try reversing the syntax. (In other words NiceName1 was the new calculated member serving as an alias to M1 in my original example.)

You will lose the ability to do drillthrough on the calc members. Vladamir can comment on other concerns he may have about this approache.

|||

Vladimir -

Why is this considered a bad practice? What other ways are there to accomplish this? You have me very curious...

Thanks,

John

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

Hi All,
I am currently transferring my Access application to SQL Server. Access allows you to declare and use aliases in the query at the same time.

e.g.
Select field1 as Alias1, field2 as Alias2, Alias1 & " " & Alias2 as Alias3 from table1;

In Access the above query will execute perfectly, no problem. However in SQL Server, if you try to run the same query it will give an error "Invalid column name Alias1" meaning that SQL Server is searching for Alias1 as a field in the table, not as an alias from the query.

My question is does SQL Server have a facility to declare and use alias directly as in Access and if no, is there a workaround?

Thanks for your time.

Regards:
Prathmeshhi

try this

Select field1 as Alias1, field2 as Alias2, field1 + ' ' + field2 as Alias3 from table1;

hope this will solve ur problem|||Hi,
Ok, I think I need to explain a bit more detail. I have got a database table that stores data about different equipments. Each equipment is identified by 3 distinct fields Area, Type, No. So a particular equipment tag would be of type:
Area+Type+No.

Now at the same time the table also holds the description of the equipment which comes from 2 fields desc1 and desc2. So the whole equimment desc would be desc1+desc2

Now on the reports the equiptag and equipment desc need to be concatenated to form one equipment number i.e. Area+type+No+Desc1+desc2

So what I wanted to do was
Select Area+type+No as Equiptag, Desc1+Desc2 As EquipDesc, EquipTag+EquipDesc As EquipNo from equipment;

but obviously SQL Server will give an error of invalid column for "EquipTag" and "EquipDesc"

So, Is there any way to do this?

Regards:
Prathmesh|||Hi,

So what I wanted to do was

Select
Area+type+No as Equiptag,
Desc1+Desc2 As EquipDesc,
EquipTag+EquipDesc As EquipNo
from
equipment;

but obviously SQL Server will give an error of invalid column for "EquipTag" and "EquipDesc"

So, Is there any way to do this?

To the best of my knowledge, you can't use an alias as part of a formula within the same SQL. You would either have to do this:

Select
Area+type+No as Equiptag,
Desc1+Desc2 As EquipDesc,
Area+type+No+Desc1+Desc2 As EquipNo
from
equipment;

or you could try creating a subquery like this:

SELECT
t.EquipTag,
t.EquipDesc,
t.EquipTag+t.EquipDesc As EquipNo
FROM
(SELECT
Area+type+No as Equiptag,
Desc1+Desc2 As EquipDesc
FROM
equipment) t

Regards,

hmscott|||Thanks hmscott,
The subquery idea is a good one. I'll give it a try. I was just curious if this could be done similar to Access or not. I must say, being an Access programmer, there are certain things in SQL Server which really annoy you. Most of my queries use this type of aliasing, so I now have to go and rewrite them to replace Aliasing.

Another thing is the "concat null yields null" thing. When you concat 2 strings and one is null, the returned string is Null. Huh!!! Why? I think this is totally stupid. In Access, this is not at all a problem. It just discards the nulls, and returns the concatenated string without nulls. Well I guess this is typical Microsoft behaviour. I tried executing the stored procedure to set the concat null yeidls null to false, but it does not work. I cannot figure out why. A similar question was posted in this forum asking why it does not work, but nobody was able to answer. If anybody has got any suggestions, please do let me know.

Thanks.

Regards:
Prathmesh|||All databases are different. All databases have things that are worse than other databases or extra things that are better than other databases. There is no reason. What is included in the SQL Standard should be the same accross databases but for anything else ...|||hi Prathmesh,

try this

SELECT ISNULL(columnwithnull,'') + nonnullcolumns from yourtable|||Hi baburaj,
Yep, that is what I am using now. However, I have decided on something else. I am planning to use SQL Server backend to Access frontend, because all my forms , reports, etc. are in Access.I am going to do all the complex join queries on SQL Server side as views and link the tables via odbc to Access using the Access "link tables" facility and the required formatting I will still do on Access side. This way I can have best of both worlds. I can make use of SQL server's performance and Access' formatting features.

Thanks to all for your help and suggestion guys.|||Another thing is the "concat null yields null" thing. When you concat 2 strings and one is null, the returned string is Null. Huh!!! Why? I think this is totally stupid. In Access, this is not at all a problem. It just discards the nulls, and returns the concatenated string without nulls. Well I guess this is typical Microsoft behaviour. I tried executing the stored procedure to set the concat null yeidls null to false, but it does not work. I cannot figure out why. A similar question was posted in this forum asking why it does not work, but nobody was able to answer. If anybody has got any suggestions, please do let me know.
Not entirely true - Access also provides the "+" concatenation operator where Null + "Something" = Null.
Rather than thinking of it as a bind you need to think through the implications. The + operator is great, for example, when putting together a csv address string for presentation - you don't need to use a load of conditional statements to exclude the comma if, for example, the address has no House Name.