How to use CASE in SQL , Syntax Error being shown

Question

I am trying to retrieve some records from table based on my query but it shows me an error

Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '>

select vd.LedgerId,(CreditAmt-DebitAmt) AS NET, CASE NET
WHEN NET > 0 THEN 'Debit' WHEN NET < 0 THEN 'Credit' ELSE 'Nil'End
from dbo.vdebit vd INNER JOIN dbo.vCredit vc  ON vd.LedgerId=vc.LedgerId

Answer 1

First you are using both a input expression and boolean expressions in your case at the same time (see here). It also looks like you are trying to assign the value of that case statement to a variable, but your syntax is incorrect. I believe this is what you're looking for:

select vd.LedgerId,(CreditAmt-DebitAmt) AS NET,
CASE WHEN NET > 0 THEN 'Debit' WHEN NET < 0 THEN 'Credit' ELSE 'Nil' End
AS NetVal
from dbo.vdebit vd INNER JOIN dbo.vCredit vc  ON vd.LedgerId=vc.LedgerId

Answer 2

When using a searched case expression, the syntax is like:

CASE
   WHEN NET > 0 THEN 'Debit'
   WHEN NET < 0 THEN 'Credit'
   ELSE 'Nil'
END

So just replace CASE NET with CASE.

Answer 3

Thanks to both Wikser and Trey as I had some syntax problem that was bring into my notice by them and after that my query still had problem.

Issue was- Net was an alias to my column and when i was checking it in case statement it was not able to find out the same column.

ERROR

select vd.LedgerId,(CreditAmt-DebitAmt) AS NET,
CASE 
   WHEN NET > 0 THEN 'Debit' 
   WHEN NET < 0 THEN 'Credit' 
   ELSE 'Nil' 
End
AS NetVal
from dbo.vdebit vd INNER JOIN dbo.vCredit vc  ON vd.LedgerId=vc.LedgerId

Correction Made

select vd.LedgerId,(CreditAmt-DebitAmt) AS NET,
CASE 
   WHEN (CreditAmt-DebitAmt) > 0 THEN 'Debit' 
   WHEN (CreditAmt-DebitAmt) < 0 THEN 'Credit' 
   ELSE 'Nil' 
End
AS NetVal
from dbo.vdebit vd INNER JOIN dbo.vCredit vc  ON vd.LedgerId=vc.LedgerId