CASE WHEN problem

hi,

use can also use ISNULL(result , default value)

ISNULL((CASE WHEN (ClaimsDetail.AllowedAmt = ClaimsDetail.NotCoveredAmt and ClaimsDetail.NotCoveredAmt != 0) then 'Denied'
WHEN (ClaimsDetail.NetAmt != 0 or ClaimsDetail.CopayAmt != 0 or ClaimsDetail.DeductAmt != 0or (ClaimsDetail.OICPaidAmount != 0 and ClaimsDetail.AllowedAmt != ClaimsDetail.NotCoveredAmt))then 'Paid'
WHEN (ClaimsDetail.AllowedAmt = ClaimsDetail.NotCoveredAmt and ClaimsDetail.NotCoveredAmt != 0) then 'Denied'
WHEN (ClaimsDetail.NetAmt != 0 or ClaimsDetail.CopayAmt != 0 or ClaimsDetail.DeductAmt != 0 or (ClaimsDetail.AllowedAmt != 0 and ClaimsDetail.AllowedAmt != ClaimsDetail.NotCoveredAmt)
or (ClaimsDetail.OICPaidAmount != 0 and ClaimsDetail.NotCoveredAmt = 0)) then 'Paid' end),0) as ClaimStatus

this will return 0 value if your expression return null value.

hope this will help you.

yes you can put a else at the end, like this:

CASE ColumnName
      WHEN 'condition1' THEN '1'
      WHEN 'condition2' THEN '2'
      ELSE '9999'
END,

The query is now working, but I found out that the field comes out NULL on a few records where all the values in that query are zero.  Can I just put in an else somehow at the end and default it to 'Paid'?

arep

Yes, I see what you did.  You took out the else CASE's.  And just used WHEN.  I am still learning how to use this.

It worked.  I think all the logic is correct.

Thanks a lot.

I did not want to do this in code, because I have a nice routine that take the datareader and creates any query into a comma delimited file.

arep

hi,

you have put "Else in wrong place.

try...

CASE WHEN (ClaimsDetail.AllowedAmt = ClaimsDetail.NotCoveredAmt and ClaimsDetail.NotCoveredAmt != 0) then 'Denied'
WHEN (ClaimsDetail.NetAmt != 0 or ClaimsDetail.CopayAmt != 0 or ClaimsDetail.DeductAmt != 0or (ClaimsDetail.OICPaidAmount != 0 and ClaimsDetail.AllowedAmt != ClaimsDetail.NotCoveredAmt))then 'Paid'
WHEN (ClaimsDetail.AllowedAmt = ClaimsDetail.NotCoveredAmt and ClaimsDetail.NotCoveredAmt != 0) then 'Denied'
WHEN (ClaimsDetail.NetAmt != 0 or ClaimsDetail.CopayAmt != 0 or ClaimsDetail.DeductAmt != 0 or (ClaimsDetail.AllowedAmt != 0 and ClaimsDetail.AllowedAmt != ClaimsDetail.NotCoveredAmt)
or (ClaimsDetail.OICPaidAmount != 0 and ClaimsDetail.NotCoveredAmt = 0)) then 'Paid' end as ClaimStatus


syntax is

Case When (condition) then result
when (condition) then result
....
else result end

hope this will help you.

 

try this: