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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
LowfatspreadCommented:
you need to name the inline table....

 FOR XML PATH('') ) as x ),1,2,'')....
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.