{"id":773,"date":"2019-06-08T20:06:00","date_gmt":"2019-06-09T04:06:00","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=773"},"modified":"2020-04-11T06:58:12","modified_gmt":"2020-04-11T14:58:12","slug":"db2-case-expression","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-case-expression\/","title":{"rendered":"Db2 CASE Expression"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 CASE<\/code> expression to add if-else logic to the queries, including simple and searched CASE<\/code> expressions.<\/p>\n\n\n\n

Introduction to Db2 CASE expression<\/h2>\n\n\n\n

A CASE<\/code> expression allows you to select an expression based on evaluation of one or more conditions. In other words, it allows you to add the if-else logic to your queries.<\/p>\n\n\n\n

Db2 supports two kinds of CASE<\/code> expressions: simple CASE<\/code> and searched CASE<\/code> expressions.<\/p>\n\n\n\n

Both simple and searched CASE<\/code> are expressions, therefore, you can use them in any clause that accepts an expression such as SELECT<\/a><\/code>, WHERE<\/a><\/code>, GROUP BY<\/a><\/code>, and HAVING<\/a><\/code> clauses.<\/p>\n\n\n\n

Simple CASE expression<\/h2>\n\n\n\n

The following shows the syntax of the simple CASE<\/code> expression:<\/p>\n\n\n

CASE expression   \n    WHEN expression_1 THEN result_1\n    WHEN expression_2 THEN result_2\n    ...\n    WHEN expression_n THEN result_n\n    [ ELSE else_result ]   \nEND<\/span>  \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, Db2 compares the expression<\/code> in the CASE<\/code> clause with each expression (expression_1<\/code>, expression_2<\/code>, …) in the WHEN<\/code> clause sequentially from top to bottom.<\/p>\n\n\n\n
Db2 returns the corresponding result in the THEN<\/code> clause (result_1<\/code>, result_2<\/code>, …) if it finds a match (expression = expression1<\/code>, expression = expression2<\/code>…). Db2 immediately stops searching once it finds a match.<\/p>\n\n\n\n
If Db2 does not find any match, it will return the else_result<\/code> in case the ELSE<\/code> clause is available. If you don’t specify the ELSE<\/code> clause, the simple CASE<\/code> expression will return NULL<\/code> when it finds no match.<\/p>\n\n\n\n
Simple CASE expression example<\/h3>\n\n\n\n
The following example returns the book title, the number of authors of each book, and a note:<\/p>\n\n\n
SELECT<\/span> \n    b.title, \n    COUNT<\/span>(a.author_id) author_count,\n    CASE<\/span> COUNT<\/span>(a.author_id)\n        WHEN<\/span> 1<\/span> THEN<\/span> 'Single Author'<\/span>\n        WHEN<\/span> 2<\/span> THEN<\/span> 'Two Authors'<\/span>\n        ELSE<\/span> 'More Than Two Authors'<\/span>\n    END<\/span> note\nFROM<\/span> books b\n INNER<\/span> JOIN<\/span> book_authors a \n    ON<\/span> a.book_id = b.book_id\nGROUP<\/span> BY<\/span> b.title\nORDER<\/span> BY<\/span> b.title;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the partial output:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
In this example, we used the simple CASE<\/code> expression to make the note<\/code> columm with the following logic:<\/p>\n\n\n\n
  • If a book has one author, the CASE<\/code> expression returns 'Single Author'<\/code>.<\/li>
  • If a book has two authors, the CASE<\/code> expression returns 'Two Authors'<\/code>.<\/li>
  • If a book has more than 2 authors, the CASE<\/code> expression returns 'More Than Two Authors'<\/code> specified in the ELSE<\/code> clause.<\/li><\/ul>\n\n\n\n
    Searched CASE expression<\/h2>\n\n\n\n
    The syntax of the searched CASE<\/code> expression is the following:<\/p>\n\n\n
    CASE  \n    WHEN expression_1 THEN result_1\n    WHEN expression_2 THEN result_2\n    ...\n    WHEN expression_n THEN result_n\n    [ ELSE else_result ]   \nEND<\/span> \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this syntax:<\/p>\n\n\n\n
    • expression_1<\/code>, expression_2<\/code>,… are Boolean expressions.<\/li>
    • result_1<\/code>, result_2<\/code>, … are possible results.<\/li><\/ul>\n\n\n\n
      The searched CASE<\/code> expression evaluates expression_1<\/code>, expression_2<\/code>… sequentially in each WHEN<\/code> clause in the specified order until an expression evaluates to true. Then, the CASE<\/code> expression returns the corresponding result and stops searching.<\/p>\n\n\n\n
      If no expression evaluates to true, the searched CASE<\/code> expression returns the result in the ELSE<\/code> clause or NULL<\/code> if you don’t specify the ELSE<\/code> clause.<\/p>\n\n\n\n
      Searched CASE expression example<\/h3>\n\n\n\n
      The following example uses the searched CASE expression to return the comments based on the book ratings:<\/p>\n\n\n
      SELECT<\/span> \n    title, \n    rating,\n    CASE<\/span> \n        WHEN<\/span> (rating >= 1<\/span> AND<\/span> rating < 2<\/span>) THEN<\/span> 'Not so good'<\/span> \n        WHEN<\/span> (rating >= 2<\/span> AND<\/span> rating < 3<\/span>) THEN<\/span> 'Limited useful information'<\/span>\n        WHEN<\/span> (rating >= 3<\/span> AND<\/span> rating < 4<\/span>) THEN<\/span> 'Good book, but nothing special'<\/span>\n        WHEN<\/span> (rating >= 4<\/span> AND<\/span> rating < 5<\/span>) THEN<\/span> 'Incredbly special'<\/span>\n        WHEN<\/span> rating = 5<\/span> THEN<\/span> 'Life changing. Must Read.'<\/span>\n        ELSE<\/span>\n            'No rating yet'<\/span>\n    END<\/span> AS<\/span> comment<\/span>\nFROM<\/span> \n    books\nORDER<\/span> BY<\/span> \n    title;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      Here is the partial result set:<\/p>\n\n\n\n
      \"db2<\/figure>\n\n\n\n
      In this tutorial, you have learned how to use the Db2 CASE<\/code> expression to add if-else logic to the queries.<\/p>\n
      \n\t
      \n\t\t
      Was this tutorial helpful ?<\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t