February 23, 2005 at 1:17 pm
Hi,
I cannot figure out what is wrong with this SQL string. It dynamically creates columns in the new table based on values in the table #ErrorType.
When I try to execute it I get the following error message indicating that there is a problem near the WHERE statement:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'AdmSrvD'.
Here is the statement:
SELECT @strSQL = ' '
SELECT @strSQL = @strSQL + 'SELECT L.[CFT Location]'
WHILE @CurCnt <=@MaxCnt
BEGIN
SELECT @Column = (SELECT ErrorName FROM #ErrorType WHERE TempErrorTypeID = @CurCnt)
SELECT @strSQL = @strSQL + ', [' + Replace((SELECT ErrorName FROM #ErrorType WHERE TempErrorTypeID = @CurCnt), ' ', '') + '] = '
SELECT @strSQL = @strSQL + 'SUM(CASE WHEN E.[Error Code] = ''' + @Column + '''Then 1 ELSE Null END)'
SELECT @CurCnt = @CurCnt + 1
END
SELECT @strSQL = @strSQL + ' INTO brenna.QAMonthSummary '
SELECT @strSQL = @strSQL + ' FROM QAAccountStore QAA LEFT JOIN QAError QAE ON QAE.ID = QAA.ID'
SELECT @strSQL = @strSQL + ' LEFT JOIN Error E ON QAE.ErrorCode = E.[Error Comment Code]'
SELECT @strSQL = @strSQL + ' LEFT JOIN Location L ON QAA.Location = L.[MNE Location]'
SELECT @strSQL = @strSQL + ' WHERE QAA.AdmSrvDate >= ' + ''''+ CONVERT(nvarchar,@RptStartDte,101) + ''' AND QAA.AdmSrvDate <= ' + '''' + CONVERT (nvarchar,@RptEndDte,101) + ''''
SELECT @strSQL = @strSQL + ' GROUP BY L.[CFT Location], L.[CFT Location]'
Thanks!
February 23, 2005 at 2:06 pm
do you mind to post what - print @strSQL - looks like after the last select
* Noel
February 23, 2005 at 2:14 pm
I'm new at this and wasn't aware of print (string) but doing this I found my problem!
It was really stooopid. I had limited the @strSQL variable to 800 so it wasn't capturing the end of the statement!
Thanks for your help!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply