Where is the Invalid syntax near the comma in this TSQL?

Question

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 14-Jan-16 11:36am

B. Clay Shannon

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 2016-01-14T18:11:00

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)