lundi 1 juillet 2019

difference between if and when statements in sql

If i have a table with a 'number' column and want to create a new column with values of either 'high', 'medium' or 'low' based on the value in the number column can i use an if/else statement to do this? If so how would this be written?

I have this code for creating the column using a when/then statement:

use master
 select *, case
 when (number > 40) then 'high'
 when (number between 30 and 40) then 'medium'
 else 'low'
 end as 'newColumn'
 from myTable

Basically i'm trying to understand the difference between if/else and when/then statements and why one would be used over the other.

This if/else code doesn't work but i don't know if it's because i'm coding it incorrectly:

use master
select *,
 if (number > 40)
 'high'
 else if (number between 30 and 40)
 'medium'
 else 'low'
 from test

Aucun commentaire:

Enregistrer un commentaire