The error is raised because you have mismatched parenthesis in the condition so from syntactical point of view the comma in the
IN
clause is outside the
IN
clause.
Instead of this
and ItemCode in (Select ItemCode from UnitProducts where Unit='BIG RED ONE')),'X')
cosider this
and ItemCode in ((Select ItemCode from UnitProducts where Unit='BIG RED ONE'),'X')
However, this is not the biggest problem. When you use
IN
clause you have to decide whether you fetch the values using a subquery or if youprovide a predefined literal list. You can't mix these.
One simple way is to modify the subquery and use
UNION
to fetch the literal values combined to the queried values. For example, consider the following
SELECT *
FROM sysobjects
WHERE id IN (SELECT id
FROM sysobjects
WHERE id > 0
UNION
SELECT -1072372588
UNION
SELECT -1064594347)