November 16, 2015 at 1:06 pm
Hi,
I need to use a Like statement with an Or statement in a where clause. below is what I am trying to do and the @ProjectType can be 'P25', 'R&D' or 'ALL'.
here is the SQL:
SELECT DISTINCT ProjectName AS Project
FROM Timesheet
WHERE
CASE
WHEN @ProjectType = 'P25, R&D' THEN
ProjectName LIKE '2%' OR ProjectName LIKE 'T[0-9][0-9]%' OR ProjectName LIKE 'T[0-9]%'
WHEN @ProjectType ='P25'
THEN
(ProjectName LIKE '2%')
ELSE
(ProjectName LIKE '%%')
END
ORDER BY Project
This is for a dropdown in SSIS to only select certain Projects depending what was selected in a previous dropdown for the @ProjectType.
This gets an error Incorrect syntax near the keyword 'LIKE'.
Thanks
David
November 16, 2015 at 1:22 pm
If I understand correctly, you want something like this:
SELECT DISTINCT ProjectName AS Project
FROM Timesheet
WHERE (@ProjectType = 'P25, R&D' AND (ProjectName LIKE '2%' OR
--ProjectName LIKE 'T[0-9][0-9]%' OR --This is included in the following
ProjectName LIKE 'T[0-9]%' ))
OR (@ProjectType ='P25' AND ProjectName LIKE '2%')
OR ProjectName LIKE '%%'
ORDER BY Project
Be aware, that this can have some performance implications which are explained in here: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Finally, you said SSIS, did you mean SSRS?
November 16, 2015 at 1:26 pm
David Tooker (11/16/2015)
Hi,I need to use a Like statement with an Or statement in a where clause. below is what I am trying to do and the @ProjectType can be 'P25', 'R&D' or 'ALL'.
here is the SQL:
SELECT DISTINCT ProjectName AS Project
FROM Timesheet
WHERE
CASE
WHEN @ProjectType = 'P25, R&D' THEN
ProjectName LIKE '2%' OR ProjectName LIKE 'T[0-9][0-9]%' OR ProjectName LIKE 'T[0-9]%'
WHEN @ProjectType ='P25'
THEN
(ProjectName LIKE '2%')
ELSE
(ProjectName LIKE '%%')
END
ORDER BY Project
This is for a dropdown in SSIS to only select certain Projects depending what was selected in a previous dropdown for the @ProjectType.
This gets an error Incorrect syntax near the keyword 'LIKE'.
Thanks
David
I suggest that you build your WHERE clause like this:
select distinct
ProjectName as Project
from Timesheet
where (@ProjectType = 'P25, R&D'
and (ProjectName like '2%'
or ProjectName like 'T[0-9][0-9]%'
or ProjectName like 'T[0-9]%'
)
)
or (@ProjectType = 'P25'
and ProjectName like '2%'
)
or ProjectName like '__';'
);
There was a problem with your final LIKE. '%%' does not mean anything more than '%', which in turn means anything or nothing. I guess that you meant two characters (use an underscore for a wildcard single character).
You should also schema-qualify your table name, as a best practice.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 16, 2015 at 1:33 pm
Phil Parkin (11/16/2015)
There was a problem with your final LIKE. '%%' does not mean anything more than '%', which in turn means anything or nothing.
Anything or nothing that is not null.
November 16, 2015 at 1:36 pm
For starters, you cannot use a case statement in a where clause in this manner.
There are more elegant solutions, but since this is for reporting, I would probably write three separate queries or use a dynamic query.
1. Three separate queries
DECLARE @ProjectType varchar(100)
If @ProjectType = 'P25, R&D' Begin
SELECT ProjectName AS Project
FROM Timesheet
WHERE ProjectName LIKE '2%' OR ProjectName LIKE 'T[0-9][0-9]%' OR ProjectName LIKE 'T[0-9]%'
GROUP BY ProjectName
End
If @ProjectType ='P25' Begin
SELECT ProjectName AS Project
FROM Timesheet
WHERE ProjectName LIKE '2%'
GROUP BY ProjectName
End
If @ProjectType ='everything else condition' Begin
SELECT ProjectName AS Project
GROUP BY ProjectName
End
2. Dynamic
DECLARE @ProjectType varchar(100)
DECLARE @SQL_Select nvarchar(max)
DECLARE @SQL_Where nvarchar(max)
DECLARE @SQL_Group_By nvarchar(max)
SET @SQL_Select = 'SELECT ProjectName AS Project
FROM Timesheet' + CHAR(10)
SET @SQL_Group_By = 'GROUP BY ProjectName'
SET @SQL_Where = '' -- Handles the everything else.
If @ProjectType = 'P25, R&D' Begin
SET @SQL_Where = 'WHERE ProjectName LIKE ''2%'' OR ProjectName LIKE ''T[0-9][0-9]%'' OR ProjectName LIKE ''T[0-9]%''' + CHAR(10)
End
If @ProjectType = 'P25' Begin
SET @SQL_Where = 'WHERE ProjectName LIKE ''2%''' + CHAR(10)
End
SET @SQL_Select = @SQL_Select + @SQL_Where + @SQL_Group_By
EXEC sp_executesql @SQL_Select
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 16, 2015 at 1:46 pm
Luis Cazares (11/16/2015)
Phil Parkin (11/16/2015)
There was a problem with your final LIKE. '%%' does not mean anything more than '%', which in turn means anything or nothing.Anything or nothing that is not null.
Thanks!
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 18, 2015 at 8:57 am
Michael L John (11/16/2015)
For starters, you cannot use a case statement in a where clause in this manner.
More specifically, CASE statements cannot return Boolean values, because SQL does not have a Boolean datatype. (Although you can model some of the aspects of a Boolean datatype with BIT.)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply