SanomaJean SanomaJean - 1 month ago 7
SQL Question

SQL Where Clause Not Returning Correct Results

SELECT * FROM [dbo].[_5200_Sanoma]
WHERE right(left([VARIABLE1)],4),2) = 'RI'
and (
([Year] = '2014' and [Period] in('10','11','12'))
or [Year] = '2015')
or (
[Year] = '2016'
and [Period] in('01','02','03','04','05','06','07','08')--,'09','10','11','12')
)
and ([VARIABLE2] IN(
'String1',
'String2',
'String3',
'String4',
'String5',
'String6',
))


I had to change a few things to be more general but for some reason the first where clause, the
right(left([VARIABLE1)],4),2) = 'RI'
isn't working because I'm getting back results where that is other two character strings.

This query has worked before I added the last where clause condition

and ([VARIABLE2] IN(
'String1',
'String2',
'String3',
'String4',
'String5',
'String6',
))


So now that I added that it's not working. Any ideas?

Answer

Try to split your statements in different lines; you probably meant to do this:

SELECT * FROM [dbo].[_5200_Sanoma] 
WHERE right(left([VARIABLE1)],4),2) = 'RI' AND
(
    ([Year] = '2014' and [Period] in('10','11','12')) OR
    ([Year] = '2015') OR
    ([Year] = '2016' and [Period] in('01','02','03','04','05','06','07','08'))
)
and ([VARIABLE2] IN ('String1','String2','String3','String4','String5','String6'))