asked
Case Statements
Hi, Would someone be able to catch my error in the case statment below? When excueting the script I recieve the error message: Msg 8114, Level 16, State 5, Line 2 Error converting data type varchar to numeric. (Case when es.gaap_prim = 'U.S. Treasury securities' then 'U.S.Treasury Securities' WHEN nullif(rtrim(spr.rate_code),'NR') IS NULL THEN mr.rate_value ELSE spr.rate_value END) = 1 THEN 'AAA' WHEN (CASE WHEN nullif(rtrim(spr.rate_code),'NR') IS NULL THEN mr.rate_value ELSE spr.rate_value END) < 5 THEN 'AA' WHEN (CASE WHEN nullif(rtrim(spr.rate_code),'NR') IS NULL THEN mr.rate_value ELSE spr.rate_value END) < 13 THEN 'A' WHEN (CASE WHEN nullif(rtrim(spr.rate_code),'NR') IS NULL THEN mr.rate_value ELSE spr.rate_value END) < 16 THEN 'BBB' ELSE 'Below BBB' END [rate classification] This is what I would like my end result to be: U.S.Securities AAA AA A BBB BB Below B I apprecaite any help. Thanks.
There's a few things here The error you are getting is because there are differing data types as possible outcomes of the case statement, but I think that is because your 2nd line reads WHEN nullif(rtrim(spr.rate_code),'NR') IS NULL THEN mr.rate_value and I think you mean (based on the rest of the `case` statement) WHEN (case when nullif(rtrim(spr.rate_code),'NR') IS NULL THEN mr.rate_value or maybe that's just a typo in your question?? Also case when nullif(rtrim(spr.rate_code),'NR') IS NULL THEN mr.rate_value can be simplified to case when rtrim(spr.rate_code) = 'NR' THEN mr.rate_value personally I think it reads better and easier
8 People are following this question.
