Case Statement Logic in SQL-CodePudding

I want to select an MP column in SQL that will be named two different things depending on each value in the column. If the MP value is between 0.6 and 0.79 I want it to be named beveledring. However, if the MP value is between 0.8 and 0.99 I want it to be named Ring. If the value is < 0.6 I want the MP term to have no value (NULL).

Additionally if the MP value is BeveledRing, I want to take the MHT value and divide it by two. However, if the MP value is null or nameless, I want there to be no change to the MHT value.

Below is the drafted code so far….

SELECT  *
,case when MP BETWEEN .60 AND .79 then ' BEVELEDRING ' 
      when MP BETWEEN .80  AND .99 then ' RING' 
      else 0 
 end as HT 
,case when HT = ' BEVELEDRING ' THEN MHT/2 else 0 end as MHT

CodePudding user response：

One error I see in the original is you cannot mix types of values in the CASE results. The database needs to be able to infer a single specific data type to use for the entire column, meaning you can't use the BEVELEDRING string as the result in one part of the CASE expression and the numeric value 0 as the result in another part of the expression.

Try this:

SELECT  *
    ,CASE WHEN MP < 0.6 THEN NULL
          WHEN MP < 0.8 THEN 'BEVELEDRING'
          WHEN MP < 1.0 then 'RING' END as HT 
    ,CASE WHEN MP >= 0.6 AND MP < 0.8 THEN MHT * 0.5 ELSE MHT END AS MHT

The above should run because it only returns strings (NULL is untyped). It should also return correct results because CASE expressions will take the result from the first WHEN conditional expression to produce a true result. This lets us simplify the comparisons to only check one boundary of each range in order. The other correction is we need to repeat the conditions for the second MHT value. SQL unfortunately does not allow us to reuse the code from the first column in that way.

One concern I still have is re-using the MHT name. Because we select the * placeholder value, and also include our own MHT expression, the result set will have two MHT columns. Most databases allow this, but a few do not and we don't know what kind of database you're using. You probably want to list out each column by hand instead of using the * placeholder. This is good practice anyway and will help avoid ambiguity in your results. I can't do that part for you because I don't know your other columns.

I am also curious what you want to do for MP if the value is >= 1.0, as that situation is not well defined in the question (right now the result will be NULL).

Finally, BETWEEN range expressions are a poor fit for inexact values like decimals or dates. Instead, common practice is use to separate comparisons on each side of the range, with a inclusive check (>=) on the lower bound of the range and an exclusive check (<) on the next value after the upper bound of the range.