Share jOOQ on Facebook
Share jOOQ on Twitter

This page in other versions: 3.9 | 3.8 | 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | Old, end-of-life releases: 3.2 | 3.1 | 3.0 | 2.6 | 2.5

The CASE expression is part of the standard SQL syntax. While some RDBMS also offer an IF expression, or a DECODE function, you can always rely on the two types of CASE syntax:

CASE WHEN AUTHOR.FIRST_NAME = 'Paulo'  THEN 'brazilian'
     WHEN AUTHOR.FIRST_NAME = 'George' THEN 'english'
                                       ELSE 'unknown'
END

-- OR:

CASE AUTHOR.FIRST_NAME WHEN 'Paulo'  THEN 'brazilian'
                       WHEN 'George' THEN 'english'
                                     ELSE 'unknown'
END
DSL
      .when(AUTHOR.FIRST_NAME.equal("Paulo"), "brazilian")
      .when(AUTHOR.FIRST_NAME.equal("George"), "english")
      .otherwise("unknown");

// OR:

DSL.choose(AUTHOR.FIRST_NAME)
   .when("Paulo", "brazilian")
   .when("George", "english")
   .otherwise("unknown");

In jOOQ, both syntaxes are supported (The second one is emulated in Derby, which only knows the first one). Unfortunately, both case and else are reserved words in Java. jOOQ chose to use decode() from the Oracle DECODE function, or choose(), and otherwise(), which means the same as else.

A CASE expression can be used anywhere where you can place a column expression (or Field). For instance, you can SELECT the above expression, if you're selecting from AUTHOR:

SELECT AUTHOR.FIRST_NAME, [... CASE EXPR ...] AS nationality
  FROM AUTHOR

The Oracle DECODE() function

Oracle knows a more succinct, but maybe less readable DECODE() function with a variable number of arguments. This function roughly does the same as the second case expression syntax. jOOQ supports the DECODE() function and emulates it using CASE expressions in all dialects other than Oracle:

-- Oracle:
DECODE(FIRST_NAME, 'Paulo', 'brazilian',
                   'George', 'english',
                   'unknown');

-- Other SQL dialects
CASE AUTHOR.FIRST_NAME WHEN 'Paulo'  THEN 'brazilian'
                       WHEN 'George' THEN 'english'
                                     ELSE 'unknown'
END




// Use the Oracle-style DECODE() function with jOOQ.
// Note, that you will not be able to rely on type-safety
DSL.decode(AUTHOR.FIRST_NAME,
    "Paulo", "brazilian",
    "George", "english",
    "unknown");

CASE clauses in an ORDER BY clause

Sort indirection is often implemented with a CASE clause of a SELECT's ORDER BY clause. See the manual's section about the ORDER BY clause for more details.

The jOOQ Logo