Click here to Skip to main content
15,392,679 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm not getting results back from a query. At first I thought it was just a bad query - that no data *should* be returned, but now it seems it could be a syntax error that's causing the problem.

When running the Stored Procedure (that the query is a part of) in Visual Studio's Server Explorer, I get data back from the first query, but this one (an update) doesn't return any data (no err msg, but no data, either). Yet, when I try to run just this portion of the Stored Procedure in LINQPad, that bosom companion of coders everywhere declaims that it has discovered the much-dreaded "*Error 102: Incorrect syntax near ','.*"

It points to this line:

SQL
and ItemCode in (Select ItemCode from UnitProducts where Unit='BIG RED ONE')),'X')


...(the only line with a ','), but I don't see what the problem is. I also tried this variation on the theme:

SQL
and ItemCode in (Select ItemCode from UnitProducts where Unit='BIG RED ONE'),'X'))


...but the err msg is the same.

Here is the entire query that I'm trying in LINQPad:

SQL
isnull((Select top 1 ItemCode
From PlatypusUnitMapping
where Unit='BIG RED ONE'
and MemberNo='42'
and MemberItemCode= '314IMPie'
and ItemCode in (Select ItemCode from UnitProducts where Unit='BIG RED ONE')),'X')
Where NHItemCode='X'


So what is wrong with this? The simpler version of the query works:

SQL
Select top 1 ItemCode 
    From PlatypusUnitMapping 
    where Unit='BIG RED ONE' 
    and MemberNo='42' 
    and MemberItemCode= '314IMPie' 
    and ItemCode in 
    (Select ItemCode from UnitProducts where Unit='BIG RED ONE')


...but I need the "isNull" jazz for a subsequent query. What syntax is invalid, and how can I make it valid/fix it?
Posted
Comments
Sascha Lefèvre 14-Jan-16 17:50pm
   
Did you actually post your 'entire query' there? Because
isnull(<something>,'X') where NHItemCode='X'
is not valid SQL.
PIEBALDconsult 14-Jan-16 18:10pm
   
You'll likely need a Common Table Expression with a CROSS JOIN. ::evil grin::
ZurdoDev 14-Jan-16 18:11pm
   
You need to post the full sql. Just from a quick glance, this stuff looks OK.
B. Clay Shannon 14-Jan-16 18:47pm
   
I had to prepend "Select" in LINQPad (in the "real" SP, it's an "Update #temp1 Set" that is there.
TenmanS14 15-Jan-16 10:34am
   
the result of a select statement isn't NULL its just an empty result set, if thats supposed to be part of a SELECT statement, have a look at using CASE.

https://msdn.microsoft.com/en-gb/library/ms181765.aspx

select blah1, blah2, CASE (Select count(ItemCode)
From PlatypusUnitMapping
where Unit='BIG RED ONE'
and MemberNo='42'
and MemberItemCode= '314IMPie'
and ItemCode in (Select ItemCode from UnitProducts where Unit='BIG RED ONE'))
WHEN 0 THEN 'X'
ELSE itemcode
END,

1 solution

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
SQL
and ItemCode in (Select ItemCode from UnitProducts where Unit='BIG RED ONE')),'X')

cosider this
SQL
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
SQL
SELECT *  
FROM sysobjects 
WHERE id IN (SELECT id 
             FROM sysobjects 
             WHERE id > 0
             UNION
             SELECT -1072372588
             UNION
             SELECT -1064594347)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900