Solved

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

Posted on 2008-10-11
2
545 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
  • 2
2 Comments
 
LVL 142

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now