T-SQL syntax error using stuff function

Hello,

I am getting a syntax error stating that I have an error near the marked comma.  I could use another set of eyes'
Msg 102, Level 15, State 1, Line 36
Incorrect syntax near ')'.
SELECT  @cols = STUFF(( SELECT DISTINCT             
                                '],[' + cast(ForecastYear as varchar)            
                        From (
								SELECT DV1.ForecastYear, C1.MetricName,  C1.MetricValue,  C1.MetricValue-C2.METRICVALUE DELTA
								FROM Metrics C1
								INNER JOIN DataVersion DV1
								ON C1.DataVersionRowID = DV1.DataVersionRowID
								INNER JOIN Metrics C2
								ON C1.MetricName = c2.MetricName
								INNER JOIN DataVersion DV2
								ON C2.DataVersionRowID = DV2.DataVersionRowID
								WHERE DV1.DataVersionID = @dvid1
								AND DV2.DataVersionID = @dvid2 								
								ORDER BY 1           
                        FOR XML PATH('') )           
                   error --   ), 1, 2, '') + ']'

Open in new window

Juan VelasquezAsked:
Who is Participating?
 
LowfatspreadCommented:
then

then
set @cols=''
SELECT  @cols = @cols +',[' +convert(char(4),forcastyear)+']'            
      from (      
                        select distinct
                        dv1.ForecastYear
                        FROM Metrics C1
                        INNER JOIN DataVersion DV1 ON C1.DataVersionRowID = DV1.DataVersionRowID
                        INNER JOIN Metrics C2 ON C1.MetricName = c2.MetricName
                        INNER JOIN DataVersion DV2 ON C2.DataVersionRowID = DV2.DataVersionRowID
                        WHERE DV1.DataVersionID = @dvid1 AND DV2.DataVersionID = @dvid2                                                
                                      ) as x
               order by 1
set @cols=right(@cols,datalength(@cols)-1)
...
0
 
Om PrakashCommented:
Try if this works
SELECT  @cols = STUFF(SELECT DISTINCT '],[' + cast(ForecastYear as varchar)            
From (
	SELECT 
		DV1.ForecastYear, 
		C1.MetricName,  
		C1.MetricValue,  
		C1.MetricValue-C2.METRICVALUE DELTA
	FROM Metrics C1
		INNER JOIN DataVersion DV1 ON C1.DataVersionRowID = DV1.DataVersionRowID
		INNER JOIN Metrics C2 ON C1.MetricName = c2.MetricName
		INNER JOIN DataVersion DV2 ON C2.DataVersionRowID = DV2.DataVersionRowID
	WHERE DV1.DataVersionID = @dvid1 AND DV2.DataVersionID = @dvid2 								
	ORDER BY 1           
FOR XML PATH('')), 1, 2, '') + ']'

Open in new window

0
 
Juan VelasquezAuthor Commented:
No it doesn't work, You need the second ( after STUFF to delineate the input string
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LowfatspreadCommented:
you need to name the inline table....

 FOR XML PATH('') ) as x ),1,2,'')....
0
 
LowfatspreadCommented:
inline table format is

select ....
 from (select .... ) as x
where ...
0
 
Juan VelasquezAuthor Commented:
Below is the new code,  It compiles but when I excecute it, I am getting an invalid column name error for forecastyear
DECLARE @dvid1 as nvarchar(MAX)= '2011-EEPort-NA-BAU-B-1 -NEMS-None-Mar 30 2010  9:09AM'
DECLARE @dvid2 as nvarchar(MAX) = '2011-EEPort-NA-BAU-T-1 -NEMS-None-Mar 30 2010  8:53AM'


Declare @strSQL varchar(max)           
DECLARE @cols varchar(2000)     

		SELECT  @cols = STUFF((SELECT DISTINCT '],[' + cast(x.ForecastYear as varchar)            
		From (
				SELECT 
				DV1.ForecastYear, C1.MetricName,  C1.MetricValue,  C1.MetricValue-C2.METRICVALUE DELTA
				FROM Metrics C1
				INNER JOIN DataVersion DV1 ON C1.DataVersionRowID = DV1.DataVersionRowID
				INNER JOIN Metrics C2 ON C1.MetricName = c2.MetricName
				INNER JOIN DataVersion DV2 ON C2.DataVersionRowID = DV2.DataVersionRowID
				WHERE DV1.DataVersionID = @dvid1 AND DV2.DataVersionID = @dvid2 								
				ORDER BY 1 FOR XML PATH('')
			 )as x), 1, 2, '') + ']'
 
        select @cols  

Open in new window

0
 
LowfatspreadCommented:
can you show what the inner select returns?
why have the extra columns if you are only interested in the forcast year?
what is the expected output?
0
 
Juan VelasquezAuthor Commented:
The inner select return.  I'll be using it to create a tabular report later on.  You are right about the number of columns.  attached is the new code along with the error message
2007
2007
2007
2008
2008
2008
2009
2009
2009 etc

DECLARE @dvid1 as nvarchar(MAX)= '2011-EEPort-NA-BAU-B-1 -NEMS-None-Mar 30 2010  9:09AM'
DECLARE @dvid2 as nvarchar(MAX) = '2011-EEPort-NA-BAU-T-1 -NEMS-None-Mar 30 2010  8:53AM'


Declare @strSQL varchar(max)           
DECLARE @cols varchar(2000)     

		SELECT  @cols = STUFF((SELECT DISTINCT '],[' + cast(x.ForecastYear as varchar )            
		From (
				SELECT 
				DV1.ForecastYear 
				FROM Metrics C1
				INNER JOIN DataVersion DV1 ON C1.DataVersionRowID = DV1.DataVersionRowID
				INNER JOIN Metrics C2 ON C1.MetricName = c2.MetricName
				INNER JOIN DataVersion DV2 ON C2.DataVersionRowID = DV2.DataVersionRowID
				WHERE DV1.DataVersionID = @dvid1 AND DV2.DataVersionID = @dvid2 								
				ORDER BY 1 FOR XML PATH('')		 )as x), 1, 2, '') + ']'


Error Message
Msg 8155, Level 16, State 2, Line 17
No column name was specified for column 1 of 'x'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'ForecastYear'.

Open in new window

0
 
LowfatspreadCommented:
remove the for xml path('') clause


the way you've written the query you'll only get 1 value in @cols

what are you intending?



0
 
Juan VelasquezAuthor Commented:
This is the entire code.  I am using @cols to supply the field names in years
DECLARE @dvid1 as nvarchar(MAX)= '2011-EEPort-NA-BAU-B-1 -NEMS-None-Mar 30 2010  9:09AM'
DECLARE @dvid2 as nvarchar(MAX) = '2011-EEPort-NA-BAU-T-1 -NEMS-None-Mar 30 2010  8:53AM'


Declare @strSQL varchar(max)           
DECLARE @cols varchar(2000)     

		SELECT  @cols = STUFF((SELECT DISTINCT '],[' + cast(x.ForecastYear as varchar )            
		From (
				SELECT 
				dv1.ForecastYear 
				FROM Metrics C1
				INNER JOIN DataVersion DV1 ON C1.DataVersionRowID = DV1.DataVersionRowID
				INNER JOIN Metrics C2 ON C1.MetricName = c2.MetricName
				INNER JOIN DataVersion DV2 ON C2.DataVersionRowID = DV2.DataVersionRowID
				WHERE DV1.DataVersionID = @dvid1 AND DV2.DataVersionID = @dvid2 								
				ORDER BY ForecastYear  FOR XML PATH('')	 	 )as x), 1, 2, '') + ']'

        select @cols  
       
         
set @strSQL = 'SELECT MetricName, ' + @cols +    
                ' from (   
                        SELECT  MetricName, ForecastYear, Delta                                      
                        FROM SELECT DV1.Forecastyear, C1.MetricName,  C1.MetricValue,  C1.MetricValue-C2.METRICVALUE DELTA
						FROM Metrics C1
						INNER JOIN DataVersion DV1
						ON C1.DataVersionRowID = DV1.DataVersionRowID
						INNER JOIN Metrics C2
						ON C1.MetricName = c2.MetricName
						INNER JOIN DataVersion DV2
						ON C2.DataVersionRowID = DV2.DataVersionRowID
						WHERE DV1.DataVersionID = ' + @dvid1 + '
						AND DV2.DataVersionID = ' + @dvid2 + ')o 
                        
                pivot(sum(Delta) for ForecastYear in (' + @cols + '))p'   
     --select @strSQL 
   exec(@strSQL)  

Open in new window

0
 
LowfatspreadCommented:
then

SELECT  @cols = @cols +',[' +convert(char(4),forcastyear)+']'            
      from (      
                        select distinct
                        dv1.ForecastYear
                        FROM Metrics C1
                        INNER JOIN DataVersion DV1 ON C1.DataVersionRowID = DV1.DataVersionRowID
                        INNER JOIN Metrics C2 ON C1.MetricName = c2.MetricName
                        INNER JOIN DataVersion DV2 ON C2.DataVersionRowID = DV2.DataVersionRowID
                        WHERE DV1.DataVersionID = @dvid1 AND DV2.DataVersionID = @dvid2                                                 
                                      ) as x
               order by 1
set @cols=right(@cols,datalength(@cols)-1)
...
0
 
Juan VelasquezAuthor Commented:
I'm getting a null value in @cols.  I'll go back to my CTE solution.  Thanks anyway
0
 
Juan VelasquezAuthor Commented:
I modified the code as you suggested, I also changed the order by statement to Order By Forecast year and now I'm getting the following error in the dynamic sql portion
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '30'.
DECLARE @dvid1 as nvarchar(MAX)= '2011-EEPort-NA-BAU-B-1 -NEMS-None-Mar 30 2010  9:09AM'
DECLARE @dvid2 as nvarchar(MAX) = '2011-EEPort-NA-BAU-T-1 -NEMS-None-Mar 30 2010  8:53AM'


Declare @strSQL varchar(max)           
DECLARE @cols varchar(max)     
set @cols=''
SELECT  @cols = @cols +',[' +convert(char(4),x.forecastyear)+']'             
      from (      
                        select distinct 
                        dv1.ForecastYear 
                        FROM Metrics C1
                        INNER JOIN DataVersion DV1 ON C1.DataVersionRowID = DV1.DataVersionRowID
                        INNER JOIN Metrics C2 ON C1.MetricName = c2.MetricName
                        INNER JOIN DataVersion DV2 ON C2.DataVersionRowID = DV2.DataVersionRowID
                        WHERE DV1.DataVersionID = @dvid1 AND DV2.DataVersionID = @dvid2                                                 
                                  ) as x      
                       order by  ForecastYear
set @cols=right(@cols,datalength(@cols)-1)

       select @cols
         
set @strSQL = 'SELECT MetricName, ' + @cols +    
                ' from (   
                        SELECT  MetricName, ForecastYear, Delta                                      
                        FROM SELECT DV1.Forecastyear, C1.MetricName,  C1.MetricValue,  C1.MetricValue-C2.METRICVALUE DELTA
						FROM Metrics C1
						INNER JOIN DataVersion DV1
						ON C1.DataVersionRowID = DV1.DataVersionRowID
						INNER JOIN Metrics C2
						ON C1.MetricName = c2.MetricName
						INNER JOIN DataVersion DV2
						ON C2.DataVersionRowID = DV2.DataVersionRowID
						WHERE DV1.DataVersionID = ' + @dvid1 + '
						AND DV2.DataVersionID = ' + @dvid2 + ')o 
                        
                pivot(sum(Delta) for ForecastYear in (' + @cols + '))p'   
     --select @strSQL 
   exec(@strSQL)  

Open in new window

0
 
Juan VelasquezAuthor Commented:
Never mind, I see where the problem is.  I have the From Metric C1 immediately after the first from clause
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.