?
Solved

vb6/sql Incorrect syntax near the keyword ‘and’

Posted on 2014-02-21
2
Medium Priority
?
1,279 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

765 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