Pivot qry Help.

[kingstonr]

Hi,

I am using a pivot query like below.
IN this I am using @SelPorts variable.If I include this variable I am getting error Incorrect syntax.
Without variable If I directly put values like [EMBIDIV],[AR] I am getting result.

If I assign in variable and try to use it I am getting syntax error in @SelPorts.
can somebody plz help in this.

Select * from (select Por.ShortName, IsNull(IsNull(SM.SecDesc, rtrim(SM.SecName)), SM.PrimarySecID) as SecName,
SM.SecID,IsNull(SM.BBSectorName, 'zOthers') AssetClass, 0.00 as MarketValue,
round(MVUSD,0) MVUSD From vwYBPositions Pos, vwSecMaster SM, Portfolio Por
where Pos.Portfolio_id = Por.Portfolio_ID and
Por.ShortName in ('EMBIDIV','AMEX','AMR')
and Pos.Security_ID = SM.SecID and MVUSD <> 0 and pos.AsOfDt = '12/27/2006' ) as D
PIVOT(SUM(MVUSD) FOR ShortName IN(@SelPorts)) AS P order by 1, 2;

 

[philhege]

Let me guess (and I must, because you didn't show us the code): @SelPorts is a comma-delimited list of sorts.

You'll have to construct the query using dynamic SQL to use the information. The IN() function understands only column-data-type literals and single-column SELECT results.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[AlexCuse]

If phil is right (and I imagine that he is) this may be of interest to you

faq183-5207

Ignorance of certain subjects is a great part of wisdom

 

[kingstonr]

Phil,

I tried using a function which gives comma separated into
table valued fun.
set @SelPorts=N'[EMBIDIV],[AMEX],[AMR]'

Select * from (select Por.ShortName, IsNull(IsNull(SM.SecDesc, rtrim(SM.SecName)), SM.PrimarySecID) as SecName,
SM.SecID,IsNull(SM.BBSectorName, 'zOthers') AssetClass, 0.00 as MarketValue,
round(MVUSD,0) MVUSD From vwYBPositions Pos, vwSecMaster SM, Portfolio Por
where Pos.Portfolio_id = Por.Portfolio_ID and
Por.ShortName in ('EMBIDIV','AMEX','AMR')
and Pos.Security_ID = SM.SecID and MVUSD <> 0 and pos.AsOfDt = '12/27/2006' ) as D
PIVOT(SUM(MVUSD) FOR ShortName IN(select * from PV_GetStrParamFromCommaseparated(@SelPorts)))
AS P order by 1, 2

Still i am getting incorrect synatx.

I made the whole thing into string and used executesql.
I am not able to find out why I am not able to run directly without constructing string.

 

[philhege]

PIVOT? I take it you're using 2K5.

Why are you hard-coding these values in one place and extracting them from a variable in another?

Um,

declare @l_vSQL varchar(2048)

set @l_vSQL =
'
Select * from (select Por.ShortName, IsNull(IsNull(SM.SecDesc, rtrim(SM.SecName)), SM.PrimarySecID) as SecName,
SM.SecID,IsNull(SM.BBSectorName, ''zOthers'') AssetClass, 0.00 as MarketValue,
round(MVUSD,0) MVUSD From vwYBPositions Pos, vwSecMaster SM, Portfolio Por
where Pos.Portfolio_id = Por.Portfolio_ID and
Por.ShortName in (''EMBIDIV'',''AMEX'',''AMR'')
and Pos.Security_ID = SM.SecID and MVUSD <> 0 and pos.AsOfDt = '12/27/2006' ) as D
PIVOT(SUM(MVUSD) FOR ShortName IN('''+replace(@SelPorts,',',''',''')+''')))
AS P order by 1, 2
'

exec (@l_vSQL)

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.