Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

incorrect syntax near keyword AS

Status
Not open for further replies.
dwg23

dwg23

Technical User
Oct 21, 2002
151
US
Hello all,
I have been asked to modify this query and add the vendor name to it, the only problem is that when I put it into SSMS to work with it
I get the error of incorrect sytntax near the keyword as and when I take out ass I get an incorrect syntax near the key word 'from'
any help would be great as I am fighting a sinus infection and can barely hold my head up.
Code: 
select top 100
 i.Item,
 i.Description,
 i.SPC,
 (select isnull(sum(invl.Quantity), 0) from ARInvoiceLine invl
  where invl.Item = i.Item and invl.DateAdded >= '01/01/2014' and invl.DateAdded <'12/30/2016'
  +
  (select isnull(sum(invlc.Quantity), 0) from ARInvoiceLineComponent invlc
   where invlc.Item = i.Item and invlc.DateAdded >= '01/01/2014' and invlc.DateAdded < '12/30/2016' as 'Qty Sold'
from IMItem i
where i.SPC like @SPC
order by 'Qty Sold' desc, i.Item
 
Sort by date Sort by votes
) is missing before the single + between the two subqueries and before "as 'Qty Sold'"

Code: 
select top 100
 i.Item,
 i.Description,
 i.SPC,
 (select isnull(sum(invl.Quantity), 0) from ARInvoiceLine invl
  where invl.Item = i.Item and invl.DateAdded >= '01/01/2014' 
  and invl.DateAdded <'12/30/2016' [highlight #FCE94F] [b])[/b] [/highlight]
  +
  (select isnull(sum(invlc.Quantity), 0) from ARInvoiceLineComponent invlc
   where invlc.Item = i.Item and invlc.DateAdded >= '01/01/2014' 
   and invlc.DateAdded < '12/30/2016' [highlight #FCE94F] [b])[/b] [/highlight] as 'Qty Sold'
from IMItem i
where i.SPC like @SPC
order by 'Qty Sold' desc, i.Item

Besides, of course @SPC has to be declared and set to a value valid as a LIKE clause pattern.

Bye, Olaf.
 
Upvote 0 Downvote
Olaf thanks for the help, but I am still getting an error
Code: 
Error executing query:
declare @SPC varchar(10)
select top 100
 i.Item,
 i.Description,
 i.SPC,
 (select isnull(sum(invl.Quantity), 0) from ARInvoiceLine invl
  where (invl.Item = i.Item and invl.DateAdded >= '01/01/2014' 
  and invl.DateAdded <'12/30/2016'  ) ) +
  (select isnull(sum(invlc.Quantity), 0) from ARInvoiceLineComponent invlc
   where invlc.Item = i.Item and invlc.DateAdded >= '01/01/2014' 
   and invlc.DateAdded < '12/30/2016'  )  as 'Qty Sold'
from IMItem i
if len(rtrim(@SPC))= 0 set @SPC = '%'
where i.SPC like @SPC
order by 'Qty Sold' desc, i.Item 
Message:
Incorrect syntax near the keyword 'where'.
Error executing query:
declare @SPC varchar(10)
select top 100
 i.Item,
 i.Description,
 i.SPC,
 (select isnull(sum(invl.Quantity), 0) from ARInvoiceLine invl
  where (invl.Item = i.Item and invl.DateAdded >= '01/01/2014' 
  and invl.DateAdded <'12/30/2016'  ) ) +
  (select isnull(sum(invlc.Quantity), 0) from ARInvoiceLineComponent invlc
   where invlc.Item = i.Item and invlc.DateAdded >= '01/01/2014' 
   and invlc.DateAdded < '12/30/2016'  )  as 'Qty Sold'
from IMItem i
if len(rtrim(@SPC))= 0 set @SPC = '%'
where i.SPC like @SPC
order by 'Qty Sold' desc, i.Item 
Message:
Incorrect syntax near the keyword 'where'.

Thanks!
 
Upvote 0 Downvote
You can't put an IF statement in the middle of a query. Try using CASE in the WHERE clause instead.

Tamar
 
Upvote 0 Downvote
hello all,
sorry
i have not gotten back to you and thanks for all the help. all the suggestions have not helped and i have not gotten back as i have been at the doctor all week.found out that my headache was not a sinus infection,, it is a huge brain tumor.i am going in for in for emergency surgery in the morning and will get back to it as soon as i can. i am supposed to be in ICU for a couple of days and we will see from there. thanks again all of you1
 
Upvote 0 Downvote
Best wishes!

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Sad news, I hope this ends good for you, all the best wishes.

Bye, Olaf.
 
Upvote 0 Downvote
Hello everyone!
got out of the hospital on Sunday and it looks like I will have some challenges, but at least my thinking seems too have cleared up some and I figured out the problem.

Thanks for all the help and good wishs!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JoPaBC
  • Locked
  • Question
Incorrect syntax near the keyword 'GROUP' 1
Replies
2
Views
177
JoPaBC
JoPaBC
ahmedsa2018
  • Locked
  • Question
Part Id 3900 take wrong technology id as 7 and it must Be 2 because Feature Name and Value Exist?
Replies
4
Views
114
mikrom
mikrom
bkrampe
  • Locked
  • Question
Round Down to nearest 16th Inch 1
Replies
7
Views
149
bkrampe
bkrampe
Cpreston
  • Locked
  • Question
Field change as caused issues to store procedure
Replies
19
Views
187
Andrzejek
Andrzejek
ahmedsa2018
  • Locked
  • Question
the goal mfrom asking question is t
Replies
0
Views
86
ahmedsa2018
ahmedsa2018

Part and Inventory Search

Sponsor

Back
Top