Solved

vb6/sql Incorrect syntax near the keyword ‘and’

Posted on 2014-02-21
2
1,199 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
2 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 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 250 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now