I am creating a dynamic query like below to compare activities inputted by User. It's working fine when Inputted Activity length is small but if Activity is too long like 'Conditioning Exercise Weight Lifting (Free Weight, Nautilus Or Universal-Type), Power
Lifting Or Body Building, Vigorous Effort (Taylor Code 210'
It gives error 'The identifier that starts with 'Conditioning Exercise Weight Lifting (Free Weight, Nautilus Or Universal-Type), Power Lifting Or Body Building, Vigorous Effort ' is too long. Maximum length is 128.'
DECLARE @ActivityToCompare nvarchar(MAX) --='Inactivity Light Meditating','Self Care Taking Medication, Sitting Or Standing'
--Square brackets are added since 1st & 2nd Activity sometimes contain bracket[eg. Chapati (Big)]
SET @ActivityToCompare= CONCAT('[' + @FirstActivity,'],[', @SecondActivity + ']')
DECLARE @Sql varchar(max)
SET @Sql = ';WITH x AS
(
SELECT LTRIM(RTRIM(MET_ACTIVITY_DESCRIPTION)) AS MET_ACTIVITY_DESCRIPTION , num, sn
FROM (
SELECT MET_ACTIVITY_DESCRIPTION,
[Exercise Category] = CONVERT(varchar(32), Category),
[MET VALUE] = CONVERT(varchar(32), MET_VALUE),
[Calories Burn/Hour] = CONVERT(varchar(32), MET_VALUE*'+ convert(varchar,@weight) + ')
FROM DIET_MET_DETAIL_MST A LEFT OUTER JOIN DIET_MET_CAT_MST B on A.Category_Code=B.Code
) AS d
UNPIVOT (num FOR sn IN
([Exercise Category], [MET VALUE], [Calories Burn/Hour])
) AS unp
)
SELECT MET_ACTIVITY_DESCRIPTION = sn,' + @ActivityToCompare + ' FROM x
PIVOT (MAX(num) FOR MET_ACTIVITY_DESCRIPTION IN (' + @ActivityToCompare + ')) AS p order by MET_ACTIVITY_DESCRIPTION;'
Rather then comparing by Text I had Compared it by Code and latter in pivot query used 'as' ie alias name to display as per requirement.
DECLARE @ActivityToCompare nvarchar(MAX) --='Inactivity Light Meditating','Self Care Taking Medication, Sitting Or Standing'
--Square brackets are added since 1st & 2nd Activity sometimes contain bracket[eg. Chapati (Big)]
--SET @ActivityToComp= CONCAT('[' + @FirstActivity,'],[', @SecondActivity + ']') --Commented since now comparing by 'Code' rather then 'MET_ACTIVITY_DESCRIPTION'
SET @ActivityToCompare= CONCAT('[' + @FirstActivityCode,'],[', @SecondActivityCode + ']')
SET @ActivityToComp = CONCAT('[' + @FirstActivityCode, + '] as [' + @FirstActivity + '],[' + @SecondActivityCode + '] as [' + @SecondActivity +']')
DECLARE @Sql varchar(max)
SET @Sql = ';WITH x AS
(
SELECT LTRIM(RTRIM(CODE)) AS MET_ACTIVITY_DESCRIPTION , num, sn
FROM (
SELECT A.Code,
[Exercise Category] = CONVERT(varchar(32), Category),
[MET VALUE] = CONVERT(varchar(32), MET_VALUE),
[Calories Burn/Hour] = CONVERT(varchar(32), MET_VALUE*'+ convert(varchar,@weight) + ')
FROM DIET_MET_DETAIL_MST A LEFT OUTER JOIN DIET_MET_CAT_MST B on A.Category_Code=B.Code
) AS d
UNPIVOT (num FOR sn IN
([Exercise Category], [MET VALUE], [Calories Burn/Hour])
) AS unp
)
SELECT MET_ACTIVITY_DESCRIPTION = sn,' + @ActivityToComp + ' INTO #TempTableTesting FROM x
PIVOT (MAX(num) FOR MET_ACTIVITY_DESCRIPTION IN (' + @ActivityToCompare + ')) AS p order by MET_ACTIVITY_DESCRIPTION;
Select * FROM #TempTableTesting '
This error is usually that you use double quotation marks in statements.
In general, I suggest that you can replace the double quotation marks in statements with single quotation marks.
You can check through the SQL statements printed out, such as If you still can't solve your issue, I suggest you give us your data table
structure and the print SQL statement for reference.
Thanks Yong. But the issue is that column name length=145 in Select query. I am converting column value as column name using pivot. Everything is working fine but if column name Length>128 it throws an error, eg take the below Select Query, it throws error
since alias name(ie Conditioning Exercise Weight Lifting (Free Weight, Nautilus Or Universal-Type), Power Lifting Or Body Building, Vigorous Effort (Taylor Code 210) length>128
Select MET_ACTIVITY_DESCRIPTION,2101 as 'Conditioning Exercise Weight Lifting (Free Weight, Nautilus Or Universal-Type), Power Lifting Or Body Building, Vigorous Effort (Taylor Code 210',13036 as 'Self Care Taking Medication, Sitting Or Standing' FROM #TempTableTesting
Member
85 Points
374 Posts
pivot table error identifier is too long. Maximum length is 128.
Dec 06, 2019 09:01 AM|geetasks|LINK
I am creating a dynamic query like below to compare activities inputted by User. It's working fine when Inputted Activity length is small but if Activity is too long like 'Conditioning Exercise Weight Lifting (Free Weight, Nautilus Or Universal-Type), Power Lifting Or Body Building, Vigorous Effort (Taylor Code 210'
It gives error 'The identifier that starts with 'Conditioning Exercise Weight Lifting (Free Weight, Nautilus Or Universal-Type), Power Lifting Or Body Building, Vigorous Effort ' is too long. Maximum length is 128.'
DECLARE @ActivityToCompare nvarchar(MAX) --='Inactivity Light Meditating','Self Care Taking Medication, Sitting Or Standing'
--Square brackets are added since 1st & 2nd Activity sometimes contain bracket[eg. Chapati (Big)]
SET @ActivityToCompare= CONCAT('[' + @FirstActivity,'],[', @SecondActivity + ']')
DECLARE @Sql varchar(max)
SET @Sql = ';WITH x AS
(
SELECT LTRIM(RTRIM(MET_ACTIVITY_DESCRIPTION)) AS MET_ACTIVITY_DESCRIPTION , num, sn
FROM (
SELECT MET_ACTIVITY_DESCRIPTION,
[Exercise Category] = CONVERT(varchar(32), Category),
[MET VALUE] = CONVERT(varchar(32), MET_VALUE),
[Calories Burn/Hour] = CONVERT(varchar(32), MET_VALUE*'+ convert(varchar,@weight) + ')
FROM DIET_MET_DETAIL_MST A LEFT OUTER JOIN DIET_MET_CAT_MST B on A.Category_Code=B.Code
) AS d
UNPIVOT (num FOR sn IN
([Exercise Category], [MET VALUE], [Calories Burn/Hour])
) AS unp
)
SELECT MET_ACTIVITY_DESCRIPTION = sn,' + @ActivityToCompare + ' FROM x
PIVOT (MAX(num) FOR MET_ACTIVITY_DESCRIPTION IN (' + @ActivityToCompare + ')) AS p order by MET_ACTIVITY_DESCRIPTION;'
PRINT(@Sql)
EXEC(@Sql)
Member
85 Points
374 Posts
Re: pivot table error identifier is too long. Maximum length is 128.
Dec 09, 2019 05:32 AM|geetasks|LINK
Rather then comparing by Text I had Compared it by Code and latter in pivot query used 'as' ie alias name to display as per requirement.
DECLARE @ActivityToCompare nvarchar(MAX) --='Inactivity Light Meditating','Self Care Taking Medication, Sitting Or Standing'
--Square brackets are added since 1st & 2nd Activity sometimes contain bracket[eg. Chapati (Big)]
--SET @ActivityToComp= CONCAT('[' + @FirstActivity,'],[', @SecondActivity + ']') --Commented since now comparing by 'Code' rather then 'MET_ACTIVITY_DESCRIPTION'
SET @ActivityToCompare= CONCAT('[' + @FirstActivityCode,'],[', @SecondActivityCode + ']')
SET @ActivityToComp = CONCAT('[' + @FirstActivityCode, + '] as [' + @FirstActivity + '],[' + @SecondActivityCode + '] as [' + @SecondActivity +']')
DECLARE @Sql varchar(max)
SET @Sql = ';WITH x AS
(
SELECT LTRIM(RTRIM(CODE)) AS MET_ACTIVITY_DESCRIPTION , num, sn
FROM (
SELECT A.Code,
[Exercise Category] = CONVERT(varchar(32), Category),
[MET VALUE] = CONVERT(varchar(32), MET_VALUE),
[Calories Burn/Hour] = CONVERT(varchar(32), MET_VALUE*'+ convert(varchar,@weight) + ')
FROM DIET_MET_DETAIL_MST A LEFT OUTER JOIN DIET_MET_CAT_MST B on A.Category_Code=B.Code
) AS d
UNPIVOT (num FOR sn IN
([Exercise Category], [MET VALUE], [Calories Burn/Hour])
) AS unp
)
SELECT MET_ACTIVITY_DESCRIPTION = sn,' + @ActivityToComp + ' INTO #TempTableTesting FROM x
PIVOT (MAX(num) FOR MET_ACTIVITY_DESCRIPTION IN (' + @ActivityToCompare + ')) AS p order by MET_ACTIVITY_DESCRIPTION;
Select * FROM #TempTableTesting '
--PRINT(@Sql)
EXEC(@Sql)
Contributor
3660 Points
1042 Posts
Re: pivot table error identifier is too long. Maximum length is 128.
Dec 09, 2019 07:21 AM|Yongqing Yu|LINK
Hi geetasks,
This error is usually that you use double quotation marks in statements.
In general, I suggest that you can replace the double quotation marks in statements with single quotation marks.
You can check through the SQL statements printed out, such as If you still can't solve your issue, I suggest you give us your data table structure and the print SQL statement for reference.
You can also refer to this link:
The identifier that starts with … is too long. Maximum length is 128
Best Regards,
YongQing.
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
85 Points
374 Posts
Re: pivot table error identifier is too long. Maximum length is 128.
Dec 09, 2019 12:47 PM|geetasks|LINK
Thanks Yong. But the issue is that column name length=145 in Select query. I am converting column value as column name using pivot. Everything is working fine but if column name Length>128 it throws an error, eg take the below Select Query, it throws error since alias name(ie Conditioning Exercise Weight Lifting (Free Weight, Nautilus Or Universal-Type), Power Lifting Or Body Building, Vigorous Effort (Taylor Code 210) length>128
Select MET_ACTIVITY_DESCRIPTION,2101 as 'Conditioning Exercise Weight Lifting (Free Weight, Nautilus Or Universal-Type), Power Lifting Or Body Building, Vigorous Effort (Taylor Code 210',13036 as 'Self Care Taking Medication, Sitting Or Standing' FROM #TempTableTesting
I had updated Column Value so that Length<120