CASE WHEN problem

Question

I get an error on the last as after the end in the statement. I am not sure what I did wrong. Can anyone help with this. My boss said I would not get it. Thanks for the help ahead of time. arep CASE WHEN ClaimsDetail . AllowedAmt = ClaimsDetail . NotCoveredAmt and ClaimsDetail . NotCoveredAmt != 0 then 'Denied' else CASE WHEN ClaimsDetail . NetAmt != 0 or ClaimsDetail . CopayAmt != 0 or ClaimsDetail . DeductAmt != 0 or ( ClaimsDetail . OICPaidAmount != 0 and ClaimsDetail . AllowedAmt != ClaimsDetail . NotCoveredAmt ) then 'Paid' else CASE WHEN ClaimsDetail . AllowedAmt = ClaimsDetail . NotCoveredAmt and ClaimsDetail . NotCoveredAmt != 0 then 'Denied' else CASE 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

Jignesh Trivedi · Answer

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.

Javeed M Shaikh · Answer

yes you can put a else at the end like this: CASE ColumnName WHEN 'condition1' THEN '1' WHEN 'condition2' THEN '2' ELSE '9999' END

A Repasky · Answer

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

A Repasky · Answer

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

Jignesh Trivedi · Answer

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.

Javeed M Shaikh · Answer

try this: SELECT Mycol = CASE 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.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' else 'NA' end col2 from mytable