incorrect syntax near keyword AS

[dwg23]

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.

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

 

[Olaf Doschke]

) is missing before the single + between the two subqueries and before "as 'Qty Sold'"

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.

 

[dwg23]

Olaf thanks for the help, but I am still getting an error

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!

 

[TamarGranor]

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

Tamar

 

[fredericofonseca]

that "if len(rtrim(@SPC))= 0 set @SPC = '%'" must be done before the select, not within it.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[dwg23]

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

 

[gmmastros]

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

 

[Olaf Doschke]

Sad news, I hope this ends good for you, all the best wishes.

Bye, Olaf.

 

[TamarGranor]

Sending wishes for a speedy and full recovery.

Tamar

 

[dwg23]

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!

 

[jbenson001]

Glad you are ok