CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Alecu
Alecu

Reputation: 2708

Sql in sql server with convert

I am trying to use convert in an where clause in the select statement. My query looks like this:

SELECT DISTINCT TOP 10  [SurveyResult].*
                        ,[Ticket].[RefNumber] 
FROM [SurveyResult] 
LEFT JOIN [Ticket] ON [SurveyResult].[TicketID] = [Ticket].[TicketID] 
JOIN [SurveyResponse] AS SurveyResponse1 ON [SurveyResult].[ResultID] = SurveyResponse1.[ResultID] 
JOIN [QuestionAnswer] AS QuestionAnswer1 ON SurveyResponse1.[AnswerID] = QuestionAnswer1.[AnswerID] 
JOIN [SurveyQuestion] AS SurveyQuestion1 ON QuestionAnswer1.[QuestionID] = SurveyQuestion1.[QuestionID]
WHERE SurveyQuestion1.[SurveyID] = [SurveyResult].[SurveyID]
    AND SurveyQuestion1.[QuestionID] = 'C86CB39A-8FE0-4FE8-B38F-17F1BE611016'
    AND CONVERT(INT, SurveyResponse1.[Response]) >= 1
    AND CONVERT(INT, SurveyResponse1.[Response]) <= 5

The problem is that I get some errors when converting the values to integer in the where statement.

I know I have some rows that don't contain numbers in the Response column but I filter those so without the convert part in the where clause I get only numbers so it works like this:

SELECT TOP 1000 [ResponseID]
  ,[ResultID]
  ,[Response]
  FROM [WFSupport].[dbo].[SurveyResponse]
  JOIN QuestionAnswer ON SurveyResponse.AnswerID = QuestionAnswer.AnswerID 
 WHERE QuestionAnswer.QuestionID = 'C10BF42E-5D51-46BC-AD89-E57BA80EECFD'

And in the results I get numbers but once I add the convert part in the statement I I get an error that it can't convert some text to numbers.

Upvotes: 2

Views: 151

Answers (3)

Devart
Devart

Reputation: 122032

Try this one -

SELECT DISTINCT TOP 10 sr.*, t.[RefNumber] 
FROM dbo.SurveyResult sr
JOIN dbo.SurveyResponse sr2 ON sr.[ResultID] = sr2.[ResultID] 
JOIN dbo.QuestionAnswer sa ON sr2.[AnswerID] = sa.[AnswerID] 
JOIN dbo.SurveyQuestion sq ON sa.[QuestionID] = sq.[QuestionID] AND sq.[SurveyID] = sr.[SurveyID]
LEFT JOIN dbo.Ticket t ON sr.[TicketID] = t.[TicketID] 
WHERE sq.[QuestionID] = 'C86CB39A-8FE0-4FE8-B38F-17F1BE611016'
    AND CAST(ISNULL(sr2.[Response], 0) AS INT) BETWEEN 1 AND 5

Upvotes: 0

David Abrahamsson
David Abrahamsson

Reputation: 326

Either do like Mark says or just have NULL values default to something numerical, this would give you a where statement like:

WHERE SurveyQuestion1.[SurveyID] = [SurveyResult].[SurveyID]
    AND SurveyQuestion1.[QuestionID] = 'C86CB39A-8FE0-4FE8-B38F-17F1BE611016'
    AND CONVERT(INT, ISNULL(SurveyResponse1.[Response],0)) BETWEEN 1 AND 5

The important part is the ISNULL() function and I also used BETWEEN to avoid duplicate converts.

Upvotes: 4

user359040
user359040

Reputation:

Try:

SELECT DISTINCT TOP 10
            [SurveyResult].*,
            [Ticket].[RefNumber]        
        FROM 
            [SurveyResult]
        LEFT JOIN [Ticket] ON [SurveyResult].[TicketID] = [Ticket].[TicketID]
        JOIN [SurveyResponse] AS SurveyResponse1
             ON [SurveyResult].[ResultID] = SurveyResponse1.[ResultID]
        JOIN [QuestionAnswer] AS QuestionAnswer1
             ON SurveyResponse1.[AnswerID] = QuestionAnswer1.[AnswerID]
        JOIN [SurveyQuestion] AS SurveyQuestion1
             ON QuestionAnswer1.[QuestionID] = SurveyQuestion1.[QuestionID]
    where  SurveyQuestion1.[SurveyID] = [SurveyResult].[SurveyID]
      AND SurveyQuestion1.[QuestionID] = 'C86CB39A-8FE0-4FE8-B38F-17F1BE611016'
      AND CASE SurveyQuestion1.[QuestionID]
               WHEN 'C86CB39A-8FE0-4FE8-B38F-17F1BE611016'
               THEN Convert(int, SurveyResponse1.[Response])
               ELSE 0
          END BETWEEN 1 AND 5

(The AND SurveyQuestion1.[QuestionID] = 'C86CB39A-8FE0-4FE8-B38F-17F1BE611016' is retained in case the query is using an index on QuestionID - if not, it can be removed, as the same condition is implicit in the subsequent CASE condition.)

Upvotes: 2

Related Questions