Solved

SQL Server syntax error message ('INCORRECT SYNTAX NEAR =') in a query

Posted on 2008-10-11
2
549 Views
Last Modified: 2012-06-21
hi, i am trying to run the following query in SQL Server 2005 Express SSME. It keeps giving me error "Syntax error near =" in the CASE statement (which is in the WHERE clause). I dont understand why...

I know it must be something pretty simple, but i cant understand what. I need ti urgently and so am offering 500points here.

Let me know if you need more info plss..


SELECT ACCOUNTS.AccountLevel, ACCOUNTS.AccountName, SALESHEADER.OrderDate, SALESHEADER.DeliveryDate, SALESHEADER.SalesHDRID, 
SALESHEADER.PrivateOrderNumber, Sum(CASE WHEN (SALESDETAIL.PackerID Is Not Null And SALESDETAIL.PackerTime Is Not Null Or 
SALESDETAIL.Quantity Is Null Or SALESDETAIL.Quantity=0) THEN 0 ELSE 1 END) AS [Left], Sum((CASE WHEN ([SALESDETAIL].[CheckerID] Is Not Null 
And [SALESDETAIL].[CheckerTime] Is Not Null Or [SALESDETAIL].[Quantity] Is Null Or [SALESDETAIL].[Quantity]=0) THEN 0 ELSE 1 END)) AS NotChecked
FROM ITEMS INNER JOIN (ACCOUNTS INNER JOIN (SALESHEADER INNER JOIN SALESDETAIL ON SALESHEADER.SalesHDRID = SALESDETAIL.SalesHDRID) 
ON ACCOUNTS.AccountID = SALESHEADER.AccountID) ON ITEMS.ItemID = SALESDETAIL.ItemID
 
WHERE (CASE WHEN ACCOUNTS.DeliveryAccountID Is Null THEN (ACCOUNTS.ParentAccountID = 8322) ELSE (ACCOUNTS.DeliveryAccountID = 8322) END)
and SALESHEADER.InvoiceStatusID = 'OR'
 
GROUP BY SALESHEADER.AccountID, ACCOUNTS.AccountLevel, ACCOUNTS.AccountName, SALESHEADER.OrderDate, SALESHEADER.DeliveryDate, 
SALESHEADER.SalesHDRID, SALESHEADER.PrivateOrderNumber, SALESHEADER.InvoiceStatusID, SALESHEADER.OrderComment
ORDER BY SALESHEADER.DeliveryDate, ACCOUNTS.AccountName

Open in new window

0
Comment
Question by:kalbal
[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
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22695620
your WHERE condition is wrong:
 WHERE (CASE WHEN ACCOUNTS.DeliveryAccountID Is Null THEN (ACCOUNTS.ParentAccountID = 8322) ELSE (ACCOUNTS.DeliveryAccountID = 8322) END)
must read: 
 WHERE ( ISNULL( ACCOUNTS.DeliveryAccountID, ACCOUNTS.ParentAccountID) = 8322 )

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22695622
note: please check out your SUM(CASE ...) statements, you mix AND and OR conditions without () , that will give you wrong results.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 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