using case in where clause

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

I have used following code.. but it have some erors...pls help me

SQL

SELECT        VoucherTypes.types, VoucherHead.VoucherNo, VoucherHead.VoucherDate, SUM(VoucherDetail.Debit) AS debit, SUM(VoucherDetail.Credit) AS credit,
                         VoucherHead.VoucherStatus
FROM  VoucherHead INNER JOIN VoucherTypes ON VoucherHead.VoucherType = VoucherTypes.vtypeid INNER JOIN
                         VoucherDetail ON VoucherHead.VoucherID = VoucherDetail.VoucherID
WHERE        (VoucherHead.VoucherDate >= @fromdate) AND (VoucherHead.VoucherDate <= @todate) AND (VoucherTypes.types = @vtype) AND (VoucherHead.Branchno = @branchid)and case(VoucherHead.VoucherStatus)when @val=1 then VoucherHead.VoucherStatus='true' when @val=2 then VoucherHead.VoucherStatus=false when @val=0 then VoucherHead.VoucherStatus=true or VoucherHead.VoucherStatus=false or VoucherHead.VoucherStatus is null

GROUP BY VoucherTypes.types, VoucherHead.VoucherNo, VoucherHead.VoucherDate, VoucherHead.VoucherStatus

pls help me to correct this...

Posted 21-May-15 20:02pm

Nijisha Kc

Updated 21-May-15 20:16pm

King Fisher

v2

Add a Solution

Comments

King Fisher 22-May-15 2:18am

show the errors.

2 solutions

Solution 1

Syntax of Case statement:

SQL

CASE column_name
WHEN condition1 THEN result1
WHEN condition2 THEN result2
  ...
ELSE result
END

I don't find any END in your query

Posted 21-May-15 20:21pm

King Fisher

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tomas Takac · Accepted Answer · 2015-05-21T20:42:00

Solution 2

People often misunderstand the purpose of CASE statement. You are using it as a control flow statement but rather it's a complex function which returns a value and you can then compare that value to something else. In your particular case I would write the condition without CASE entirely:

SQL

WHERE
...
    AND (
        @val = 3
        OR (@val = 2 AND VoucherHead.VoucherStatus='false')
        OR (@val = 1 AND VoucherHead.VoucherStatus='true')
    )

Posted 21-May-15 20:42pm

Tomas Takac

Updated 21-May-15 21:36pm

v2

Comments

Nijisha Kc 22-May-15 3:31am

sory for discribing my question...here the variable @val can take values 1, 2, 3. .if @val=1 i want to show the rows in which voucherstatus='true' or if @val=2 i want to show the rows in which voucherstatus='false' or @val=3 i want to show the rows in which voucherstatus='true' or false or null. (ie if @val=3 it retrive all rows )

Tomas Takac 22-May-15 3:36am

That's a rather subtle change but well, see my updated answer.

Nijisha Kc 22-May-15 4:24am

Thanks alot..i lost 2 days for this.If u dont mind can u explain this query.iam confusing.

Tomas Takac 22-May-15 4:41am

I don't see what there is to explain. You need to understand boolean logic however. I assume it's clear for 1 and 2. If you pass 3 then the above condition is true for all rows irrespective of VoucherStatus. All three conditions are connected with OR so one of them needs to be true for the row to be selected.