[code]
SET ANSI_NULLS off
SET QUOTED_IDENTIFIER off
GO
DECLARE
@SqlStmt NVARCHAR(max),
@SqlStmt2 NVARCHAR(max),
@InterimHoldingsPhase1TableName NVARCHAR(256),
@SAM102TableName NVARCHAR(256),
@ClosePrice NVARCHAR(256),
@w NVARCHAR(3),
@p NVARCHAR(3),
@b NVARCHAR(3),
@s NVARCHAR(3),
@c NVARCHAR(3),
@o NVARCHAR(3),
@f NVARCHAR(3)
SELECT @w = "'W'"
SELECT @p = "'P'"
SELECT @b = "'B'"
SELECT @s = "'S'"
SELECT @c = "'C'"
SELECT @o = "'O'"
SELECT @f = "'F'"
SELECT @SAM102TableName = 'SSCSAM102Table'
SELECT @InterimHoldingsPhase1TableName = 'InterimHoldingsTable3 '
SELECT @ClosePrice = "'%CLOSE PRICE%'"
SELECT @SQLStmt ='
SELECT
SSB_FUND_NUM
,ASSET_ID
,ASSET_GROUP
,SSB_TRADE_ID
,SUM(UNRLZD_GNLS_BTL) AS UNRLZD_GNLS_BTL
,SUM(MKTVAL_BTL) AS MKTVAL_BTL
INTO #AggregatedData
FROM ' +@SAM102TableName + '
WHERE ASSET_GROUP != ' + @p + ' AND ASSET_GROUP != ' + @w + ' AND
ASSET_ID IN (SELECT ASSET_ID from ' +@InterimHoldingsPhase1TableName+ ')
AND SSB_FUND_NUM IN(SELECT SSB_FUND_NUM from ' +@InterimHoldingsPhase1TableName+ ')
GROUP BY SSB_FUND_NUM
,ASSET_ID,ASSET_GROUP,SSB_TRADE_ID
--order by SSB_FUND_NUM, ASSET_ID,ASSET_GROUP
UPDATE ' + @InterimHoldingsPhase1TableName + ' SET
'+@InterimHoldingsPhase1TableName +'.MarketValue = ad.MKTVAL_BTL
FROM #AggregatedData ad
WHERE ad.SSB_FUND_NUM = '+@InterimHoldingsPhase1TableName +'.SSB_FUND_NUM
AND ad.ASSET_ID = '+@InterimHoldingsPhase1TableName +'.ASSET_ID
AND (ad.ASSET_GROUP = '+@b+' OR ad.ASSET_GROUP = '+@s+'
OR ad.ASSET_GROUP = '+@c+' OR ad.ASSET_GROUP = '+@o+')
AND ad.SSB_FUND_NUM in (SELECT FundCode from SSCInterimAttributteTable where UPPER(AttributeNames) like '+@ClosePrice+')
UPDATE ' + @InterimHoldingsPhase1TableName + ' SET
'+@InterimHoldingsPhase1TableName +'.MarketValue = ad.MKTVAL_BTL
FROM #AggregatedData ad
WHERE ad.SSB_FUND_NUM = '+@InterimHoldingsPhase1TableName +'.SSB_FUND_NUM
AND ad.ASSET_ID = '+@InterimHoldingsPhase1TableName +'.ASSET_ID
AND (ad.ASSET_GROUP = '+@b+' OR ad.ASSET_GROUP = '+@s+'
OR ad.ASSET_GROUP = '+@c+' OR ad.ASSET_GROUP = '+@o+')
AND ad.SSB_TRADE_ID = '+@InterimHoldingsPhase1TableName +'.SSB_TRADE_ID
AND ad.SSB_FUND_NUM in (SELECT FundCode from SSCInterimAttributteTable where UPPER(AttributeNames) like '+@ClosePrice+')
UPDATE ' + @InterimHoldingsPhase1TableName + ' SET
'+@InterimHoldingsPhase1TableName +'.MarketValue = ad.UNRLZD_GNLS_BTL
FROM #AggregatedData ad
WHERE ad.SSB_FUND_NUM = '+@InterimHoldingsPhase1TableName +'.SSB_FUND_NUM
AND ad.ASSET_ID = '+@InterimHoldingsPhase1TableName +'.ASSET_ID
AND (ad.ASSET_GROUP = '+@f+' OR ad.ASSET_GROUP = '+@p+'
OR ad.ASSET_GROUP = '+@w+' )
AND ad.SSB_FUND_NUM in (SELECT FundCode from SSCInterimAttributteTable where UPPER(AttributeNames) like '+@ClosePrice+')
UPDATE ' + @InterimHoldingsPhase1TableName + ' SET
'+@InterimHoldingsPhase1TableName +'.MarketValue = ad.UNRLZD_GNLS_BTL
FROM #AggregatedData ad
WHERE ad.SSB_FUND_NUM = '+@InterimHoldingsPhase1TableName +'.SSB_FUND_NUM
AND ad.ASSET_ID = '+@InterimHoldingsPhase1TableName +'.ASSET_ID
AND (ad.ASSET_GROUP = '+@f+' OR ad.ASSET_GROUP = '+@p+'
OR ad.ASSET_GROUP = '+@w+' )
AND ad.SSB_TRADE_ID = '+@InterimHoldingsPhase1TableName +'.SSB_TRADE_ID
AND ad.SSB_FUND_NUM in (SELECT FundCode from SSCInterimAttributteTable where UPPER(AttributeNames) like '+@ClosePrice+')
UPDATE ' + @InterimHoldingsPhase1TableName + ' SET
'+@InterimHoldingsPhase1TableName +'.UnrealizedValue = ad.UNRLZD_GNLS_BTL
FROM #AggregatedData ad
WHERE ad.SSB_FUND_NUM = '+@InterimHoldingsPhase1TableName +'.SSB_FUND_NUM
AND ad.ASSET_ID = '+@InterimHoldingsPhase1TableName +'.ASSET_ID
AND (ad.ASSET_GROUP = '+@b+' OR ad.ASSET_GROUP = '+@c+' OR ad.ASSET_GROUP = '+@f+'
OR ad.ASSET_GROUP = '+@o+' OR ad.ASSET_GROUP = '+@s+' OR ad.ASSET_GROUP = '+@p+'
OR ad.ASSET_GROUP = '+@w+' )
AND ad.SSB_FUND_NUM in (SELECT FundCode from SSCInterimAttributteTable where UPPER(AttributeNames) like '+@ClosePrice+')
UPDATE ' + @InterimHoldingsPhase1TableName + ' SET
'+@InterimHoldingsPhase1TableName +'.UnrealizedValue = ad.UNRLZD_GNLS_BTL
FROM #AggregatedData ad
WHERE ad.SSB_FUND_NUM = '+@InterimHoldingsPhase1TableName +'.SSB_FUND_NUM
AND ad.ASSET_ID = '+@InterimHoldingsPhase1TableName +'.ASSET_ID
AND (ad.ASSET_GROUP = '+@b+' OR ad.ASSET_GROUP = '+@c+' OR ad.ASSET_GROUP = '+@f+'
OR ad.ASSET_GROUP = '+@o+' OR ad.ASSET_GROUP = '+@s+' OR ad.ASSET_GROUP = '+@p+'
OR ad.ASSET_GROUP = '+@w+' )
AND ad.SSB_TRADE_ID = '+@InterimHoldingsPhase1TableName +'.SSB_TRADE_ID
AND ad.SSB_FUND_NUM in (SELECT FundCode from SSCInterimAttributteTable where UPPER(AttributeNames) like '+@ClosePrice+')
DROP TABLE #AggregatedData'
SELECT @SQLStmt2 ='
SELECT
SSB_FUND_NUM
,ASSET_ID
,ASSET_GROUP
,SUM(UNRLZD_GNLS_BTL) AS UNRLZD_GNLS_BTL
,SUM(MKTVAL_BTL) AS MKTVAL_BTL
INTO #AggregatedData2
FROM ' +@SAM102TableName + '
WHERE ASSET_GROUP != ' + @p + ' AND ASSET_GROUP != ' + @w + ' AND
ASSET_ID IN (SELECT ASSET_ID from ' +@InterimHoldingsPhase1TableName+ ')
AND SSB_FUND_NUM IN(SELECT SSB_FUND_NUM from ' +@InterimHoldingsPhase1TableName+ ')
AND SSB_TRADE_ID IS NOT NULL
GROUP BY SSB_FUND_NUM
,ASSET_ID, ASSET_GROUP
--order by SSB_FUND_NUM, ASSET_ID
UPDATE ' + @InterimHoldingsPhase1TableName + ' SET
'+@InterimHoldingsPhase1TableName +'.UnrealizedValue = ad.UNRLZD_GNLS_BTL,
'+@InterimHoldingsPhase1TableName +'.MarketValue = ad.MKTVAL_BTL
FROM #AggregatedData2 ad
WHERE ad.SSB_FUND_NUM = '+@InterimHoldingsPhase1TableName +'.SSB_FUND_NUM
AND ad.ASSET_ID = '+@InterimHoldingsPhase1TableName +'.ASSET_ID
AND (ad.ASSET_GROUP = '+@c+')
AND ad.SSB_FUND_NUM in (SELECT FundCode from SSCInterimAttributteTable where UPPER(AttributeNames) like '+@ClosePrice+')
DROP TABLE #AggregatedData2'
EXECUTE (@SQLStmt)
EXECUTE (@SQLStmt2)
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
[\code]
I have the above code it was working fine. now all of a sudden im getting the following error between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the is operation.
it seem to be jut happening on this line SELECT @p = "'P'". any idea why.