Solved

vb6/sql Incorrect syntax near the keyword ‘and’

Posted on 2014-02-21
2
1,222 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 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

749 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