Incorrect syntax near 'pvt' error

Question

Post reply

Incorrect syntax near 'pvt' error

bobbydave

SSC Enthusiast

Points: 118

June 28, 2019 at 9:09 am

#3658358

I keep getting an error and when I click on the error, it brings me to @PropertyTable on the PropertyName object.

Any ideas whats up with this code?

DROP TABLE #DATA


DECLARE @DocNum VARCHAR(100), @DocVersion VARCHAR(10)

SET@DocNum = 'IQ-0005'
SET @DocVersion = '0.1'
SET NOCOUNT ON;

DECLARE @DocNumSrch NVARCHAR(MAX) = '%' + @DocNum + '%'


DECLARE @PivotProperties VARCHAR(1000)
DECLARE @SQL NVARCHAR(MAX)

CREATE TABLE #Data (
TestCaseIdINT,
TestCaseNumberNVARCHAR(MAX),
PropertyNameNVARCHAR(MAX),
PropertyValueNVARCHAR(MAX),
DispositionNVARCHAR(MAX)
)

DECLARE @PropertyId TABLE(
PropertyElementIdINT,
PropertyNameNVARCHAR(MAX)
)

INSERT INTO @PropertyId
SELECT DISTINCT
op.PropertyElementId,
op.PropertyName
FROM
dbo.ObjectProperties AS op
WHERE 
PropertyName IN ( SELECT SETTINGVALUE FROM dbo.Reporting_Configuration WHERE ReportName = 'Incident Summary')


-- Pivot Properties
SET @PivotProperties = (
SELECT DISTINCT
  STUFF((SELECT distinct '],[' + rc.SettingValue
 FROM 
dbo.Reporting_Configuration AS rc
WHERE
rc.ReportName = 'Incident Summary' AND
rc.SettingName IN ('Test' , 'Description')
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,2,'') + ']' ObjectProperties 
from Reporting_Configuration rc)


INSERT INTO #Data
SELECT
d.DocumentId,
d.DocumentNumber,
o.PropertyName,
o.PropertyValue,
d.DocumentCurrentStatus

FROM 
dbo.ObjectProperties AS o
JOIN dbo.Documents AS d ON
o.PropertyObjectId = d.DocumentObjectId
JOIN @PropertyId AS prop ON
o.PropertyElementId = prop.PropertyElementId
WHERE
(d.DocumentNumber LIKE @DocNumSrch) AND
(d.DocumentVersion = @DocVersion)

-- Pivot the field properties. Use Dynamic SQL to allow us to use field names based on the @PivotProperties parameter
SET @SQL = 
'SELECT * INTO ##PRPropertiesPivot FROM (
SELECT 
pvt.TestCaseId,
    pvt.[Test Case],
pvt.Description,
CASE
WHEN pvt.Disposition = ''In Pre-Approval'' THEN ''Open''
WHEN pvt.Disposition = ''Pre-Approved'' THEN ''Open''
WHEN pvt.Disposition = ''In Test'' THEN ''Open''
WHEN pvt.Disposition = ''In Approval'' THEN ''Open''
WHEN pvt.Disposition = ''Approved'' THEN ''Closed''
ELSE  pvt.Disposition
END AS ''Disposition'',
'''' AS ''BlankField''
 FROM 
(SELECT
Tc.TestCaseId,
Tc.TestCaseNumber,
Tc.PropertyName,
Tc.Disposition,
CAST(Tc.PropertyValue AS NVARCHAR(MAX)) AS PropertyValue
FROM
#Data AS  Tc) AS Tc
PIVOT
(MIN(PropertyValue)
FOR PropertyName IN (' + @PivotProperties + ')) AS pvt'

EXEC (@SQL)

Viewing 7 posts - 1 through 6 (of 6 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

If you change EXEC (@SQL) to PRINT @sql, do you still get the error? If not, the error is in the dynamic SQL. If you eyeball the @sql output, you will probably be able to see what the syntax error is. If you can't, please posit it here.

John

bobbydave SSC Enthusiast Points: 118 More actions · Answer 2

I get this result. Still no closer to figuring it out.

SELECT * INTO ##PRPropertiesPivot FROM (
SELECT 
pvt.TestCaseId,
    pvt.[Test Case],
pvt.Description,
CASE
WHEN pvt.Disposition = 'In Pre-Approval' THEN 'Open'
WHEN pvt.Disposition = 'Pre-Approved' THEN 'Open'
WHEN pvt.Disposition = 'In Test' THEN 'Open'
WHEN pvt.Disposition = 'In Approval' THEN 'Open'
WHEN pvt.Disposition = 'Approved' THEN 'Closed'
ELSE  pvt.Disposition
END AS 'Disposition',
'' AS 'BlankField'
 FROM 
(SELECT
Tc.TestCaseId,
Tc.TestCaseNumber,
Tc.PropertyName,
Tc.Disposition,
CAST(Tc.PropertyValue AS NVARCHAR(MAX)) AS PropertyValue
FROM
#Data AS  Tc) AS Tc
PIVOT
(MIN(PropertyValue)
FOR PropertyName IN ([Description],[Test Case])) AS pvt

bobbydave SSC Enthusiast Points: 118 More actions · Answer 3

Should there be a closing bracket

SELECT * INTO ##PRPropertiesPivot FROM (

I cant find where it would sit

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Maybe you just need to lose "FROM (" from the first line?

John

Edit - or do you just need a closing bracket at the very end?

This reply was modified 5 years, 5 months ago by John Mitchell-245523.

Thom A SSC Guru Points: 99133 More actions · Answer 5

Use PRINT or SELECT to return the value of @SQL and then debug your dynamic statement. Then you can propagate that to your statement that generates the dynamic statement.

On a different note wrapping a value with '[' + ColumnName + ']' doesn't make it safe from injection. Just like a single quote with a literal varchar a bracket can be escaped and injected into. As you're using SQL Server 2017 you can make use of STRING_AGG as well to make that statement far simpler:

SELECT @PivotProperties = STRING_AGG(QUOTENAME(rc.SettingValue), ',')
FROM dbo.Reporting_Configuration rc
WHERE rc.ReportName = 'Incident Summary'
  AND rc.SettingName IN ('Test', 'Description');

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

rVadim Hall of Fame Points: 3969 More actions · Answer 6

There are 7 opening brackets '(' but only 6 closing ')'. I think you need to add closing bracket at the end with alias.

--Vadim R.