Aliases in OPENQUERY

Question

Post reply

Aliases in OPENQUERY

Igor Micev

SSC-Dedicated

Points: 33110
More actions
April 9, 2014 at 7:31 am

#306221

Hi All
I have the following query
SELECT [KPI].*
FROM
OPENQUERY(LINKED_OLAP,'SELECT
HEAD(TAIL(DESCENDANTS
(TAIL([Time].[CalendarMonth].[Year],1), [Time].[CalendarMonth].[Month]),4),3) ON COLUMNS,
([Game].[Game Code].&[1] ,
{
[Measures].[ActiveUsers],
[Measures].[NewUsers]
})ON ROWS
FROM [CQGaming]') AS [KPI]
the output is
[Game].[Game Code].[Game Code].[MEMBER_CAPTION][Measures].[MeasuresLevel].[MEMBER_CAPTION][Time].[CalendarMonth].[Month].&[2014]&[2][Time].[CalendarMonth].[Month].&[2014]&[3][Time].[CalendarMonth].[Month].&[2014]&[4]
1ActiveUsers40304599 5130
1NewUsers1009987
the last three columns are dynamically generated because they change during time. Next month they will be different.
I like to introduce aliases for them and to have them in the select as 'TWO_MONTHS_AGO','ONE_MONTH_AGO', 'CURRENT_MONTH'
Any suggestion...
I wonder if exists something like [KPI].(0), [KPI].(1), and etc.. of the OPENQUERY to get the selected columns by their ordering number...
Thanks in advance
Igor
Igor Micev,My blog: www.igormicev.com

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

just stick the results in a temp table, where that table has the aliases already like this:

IF OBJECT_ID('tempdb.[dbo].[#tmp]') IS NOT NULL

DROP TABLE [dbo].[#tmp]

GO

CREATE TABLE [dbo].[#tmp] (

[GAMECODE] INT NULL,

[MEMBER_CAPTION] VARCHAR(100) NULL,

[TWO_MONTHS_AGO] INT NULL,

[ONE_MONTH_AGO] INT NULL,

[CURRENT_MONTH] INT NULL)

INSERT INTO #tmp ([GAMECODE],[MEMBER_CAPTION],[TWO_MONTHS_AGO],[ONE_MONTH_AGO],[CURRENT_MONTH])

SELECT [KPI].*

FROM

OPENQUERY(LINKED_OLAP,'SELECT

HEAD(TAIL(DESCENDANTS

(TAIL([Time].[CalendarMonth].[Year],1), [Time].[CalendarMonth].[Month]),4),3) ON COLUMNS,

([Game].[Game Code].&[1] ,

{

[Measures].[ActiveUsers],

[Measures].[NewUsers]

})ON ROWS

FROM [CQGaming]') AS [KPI]

SELECT * FROM #tmp

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!