Solved

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

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mySql Syntax 7 44
Move SQL 2005 Express to Server 2012R2 19 100
combine an MS SQL string in Idera DM 9 48
MS SQLK Server multi-part identifier cannot be bound 5 25
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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.
A short film showing how OnPage and Connectwise integration works.

914 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

12 Experts available now in Live!

Get 1:1 Help Now