Solved

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

Posted on 2008-10-11
2
550 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

724 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