1
Answer

"Incorrect Syntax near 'NA'

Ramco Ramco

Ramco Ramco

4d
52
1

Hi

 In below code i am getting error "Incorrect Syntax near 'NA' in set @query = syntax

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(T2."U_A_M") 
            FROM Opch T0 inner join Pch1 T1 on T0."Docentry" = T1."DocEntry"
	inner join Oitm T2 on T1."ItemCode" = T2."ItemCode"
	where T2.U_A_M <> 'NA'
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
    
set @query = 'SELECT itemcode, ' + @cols + ' from 
            (
                SELECT T1."ItemCode",T1."LineTotal", T2."U_A_M"
    FROM Opch T0 inner join Pch1 T1 on T0."Docentry" = T1."DocEntry"
	inner join Oitm T2 on T1."ItemCode" = T2."ItemCode"
	where T2.U_A_M <> 'NA'
           ) x
            pivot 
            (
                 sum(linetotal)
                for category in (' + @cols + ')
            ) p '


execute(@query)

Thanks

Answers (1)