Solved

Whats wrong with this SQL statement?

Posted on 2009-05-06
2
200 Views
Last Modified: 2012-05-06
I am trying to use a CASE statement in a WHERE clause, I have it to this point but when I try this I get the error "Incorrect syntax near the keyword 'END'".  And if I remove the single quotes surrounding the (companid=0....) I get a message saying the '=' is wrong.

I'm stuck... lol

p.s. This is the code in a stored procedure and all the variables are declared and correct.
BEGIN

	SELECT  

		count (DISTINCT (dbo.Member.ID)) AS mcount

	FROM 

		dbo.Member

	WHERE

		((SELECT count(billingid) FROM billingdata 

		  WHERE ((billingmonth>=@MONTH AND billingyr=@YEAR-1) OR (billingmonth<@MONTH AND billingyr=@YEAR)) AND (billingid=dbo.member.id)) >= 1

		) AND

		member.memberstatus=101 AND

		CASE

			WHEN @mtype='M' THEN '(companyid=0 AND departmentid=0 AND householdid=0)'

			WHEN @mtype='J' THEN '(companyid=0 AND departmentid=0 AND householdid>0)'

			WHEN @mtype='C' THEN '(companyid>0 AND departmentid=0 AND householdid=0)'

			WHEN @mtype='D' THEN '(companyid>0 AND departmentid>0 AND householdid=0)'

		END

END

Open in new window

0
Comment
Question by:StuartMc77
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 65 total points
ID: 24312891
it's wrong :)
BEGIN
      SELECT  
            count (DISTINCT (dbo.Member.ID)) AS mcount
      FROM 
            dbo.Member
      WHERE
            ((SELECT count(billingid) FROM billingdata 
              WHERE ((billingmonth>=@MONTH AND billingyr=@YEAR-1) OR (billingmonth<@MONTH AND billingyr=@YEAR)) AND (billingid=dbo.member.id)) >= 1
            ) 
       AND member.memberstatus=101 
       AND (  (@mtype='M'  AND companyid=0 AND departmentid=0 AND householdid=0)
           OR (@mtype='J'  AND companyid=0 AND departmentid=0 AND householdid>0)
           OR (@mtype='C'  AND companyid>0 AND departmentid=0 AND householdid=0)
           OR (@mtype='D'  AND companyid>0 AND departmentid>0 AND householdid=0)
           )
           
END

Open in new window

0
 
LVL 1

Author Closing Comment

by:StuartMc77
ID: 31578383
LMAO... that makes so much sense... thank you very much... sorry for the low points, :o( I'm still trying to build them up
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PL/SQL - Leading zeros 7 58
SQL Select Query help 3 43
SQL to update characters in table column 6 91
How can I exclude some wording in a like statement? 39 66
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

910 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

23 Experts available now in Live!

Get 1:1 Help Now