Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

vb6/sql Incorrect syntax near the keyword ‘and’

Posted on 2014-02-21
2
Medium Priority
?
1,300 Views
Last Modified: 2014-02-21
I encounter the following error...

Run-time error ‘-2147217900 (80040e14)’:
Incorrect syntax near the keyword ‘and’.

Here's the code...

Select item.Inv_Units_Pack as UnitsPack, Sum(item.QOH) as TotQty, Sum(item.QOH) as Allocated, 0 as UnAllocated
from rpr_dev.dbo.item With (NOLOCK)
left outer join field_option_detail
on item.fo_allocation = field_option_detail.id_field_option_detail
inner join user_comp_permission
on item.id_company = user_comp_permission.id_company
WHERE id_item_header = 11354
Group BY item.inv_units_Pack and user_comp_permission.id_user = 37

 Any Ideas on what is causing this error?

Thanks in advance
0
Comment
Question by:tesla764
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 total points
ID: 39877053
>Group BY item.inv_units_Pack and user_comp_permission.id_user = 37
There are two problems with the above statement:
1  Separate columns with commas , and not the word AND
2  I don't see id_user in the SELECT clause, so it can't be in the GROUP BY.   Also, if your intent is to filter the return sets with only the id_user = 37, than that belongs in the WHERE clause.
Select item.Inv_Units_Pack as UnitsPack, Sum(item.QOH) as TotQty, Sum(item.QOH) as Allocated, 0 as UnAllocated 
from rpr_dev.dbo.item With (NOLOCK) 
left outer join field_option_detail 
on item.fo_allocation = field_option_detail.id_field_option_detail 
inner join user_comp_permission 
on item.id_company = user_comp_permission.id_company 
WHERE id_item_header = 11354 and user_comp_permission.id_user = 37
Group BY item.inv_units_Pack 

Open in new window

0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 1000 total points
ID: 39877054
I think you have this bit muddled:
WHERE id_item_header = 11354 
Group BY item.inv_units_Pack and user_comp_permission.id_user = 37

Open in new window

Should probably be:
WHERE id_item_header = 11354  and user_comp_permission.id_user = 37
Group BY item.inv_units_Pack 

Open in new window

0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question