Column name as variable

Does anyone know if I can use a variable for a column name in a query?
I'm wanting to reuse some code that updates a table, but, depending on
conditions, I want it to update a different column.
Something like:
declare @.col_name as '
Select @.col_name = "last_week"
update tblTest set @.col_name = blah blah blah
Perspiring minds want to know.
DSHello,
You may need to use dynamic sql for this. Take a look into EXEC and
SP_EXECUTESQL in books online
Thanks
Hari
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178038066.444666.41510@.y5g2000hsa.googlegroups.com...
> Does anyone know if I can use a variable for a column name in a query?
> I'm wanting to reuse some code that updates a table, but, depending on
> conditions, I want it to update a different column.
> Something like:
> declare @.col_name as '
> Select @.col_name = "last_week"
> update tblTest set @.col_name = blah blah blah
> Perspiring minds want to know.
> DS
>|||On May 1, 9:54 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hello,
> You may need to use dynamic sql for this. Take a look into EXEC and
> SP_EXECUTESQL in books online
> Thanks
> Hari
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178038066.444666.41510@.y5g2000hsa.googlegroups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Gracias. That looks promising.

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