Previous Next JavaScript must be enabled to correctly display this content

## Case Expressions

The searched CASE expression is similar to the if-then-else statements of traditional programming languages. It consists of a number of WHEN-THEN pairs, followed by an optional ELSE clause at the end. Each WHEN expression is a condition, i.e., it must return BOOLEAN. The THEN expressions as well as the ELSE expression may return any sequence of items. The CASE expression is evaluated by first evaluating the WHEN expressions from top to bottom until the first one that returns true. If it is the i-th WHEN expression that returns true, then the i-th THEN expression is evaluated and its result is the result of the whole CASE expression. If no WHEN expression returns true, then if there is an ELSE, its expression is evaluated and its result is the result of the whole CASE expression; Otherwise, the result of the CASE expression is the empty sequence.

Example:

    SELECT
        fullName,
        CASE
            WHEN NOT exists bag.bagInfo.flightLegs[0]
            THEN "you have no bag info"
            WHEN NOT exists bag.bagInfo.flightLegs[1]
            THEN "you have one hop"
            WHEN NOT exists bag.bagInfo.flightLegs[2]
            THEN "you have two hops."
            ELSE "you have three hops."
        END AS NUMBER_HOPS
    FROM BaggageInfo bag WHERE ticketNo=1762340683564

Explanation: You want to determine how many transits are there for the passenger `bagInfo` using a CASE statement. If the `flightLegs` array has no elements, then the passenger has no bag data. When the `flightLegs` array has only one element, then there is only one transit point. Similarly, if the `flightLegs` array has two elements, then there is two hops. Else there is three transit points. Here you assume that a bag can have at the most three transit points/hops.

Output:

    {"fullName":"Mary Watson","NUMBER_HOPS":"you have two hops."}

Example 2: Write a query to alert the system to update the `tagNum` of passengers if the existing value is not a string.

    SELECT bag.bagInfo[].tagNum,
    CASE
       WHEN bag.bagInfo[0].tagNum is of type (NUMBER)
       THEN "Tagnumber is not a STRING. Update the data"
       ELSE "Tagnumber has correct datatype"
       END AS tag_NUM_TYPE
    FROM BaggageInfo bag

Explanation: The `tagNum` of passengers in the `bagInfo` schema is a STRING data type. But the application could take in a NUMBER value as the value of `tagNum` by mistake. The query uses "is of type" operator to capture this and prompts the system to update the `tagNum` if the existing value is not a string.

Output (only few rows are shown for brevity).

    {"tagNum":"17657806240001","tag_NUM_TYPE":"Tagnumber has correct datatype"}
    {"tagNum":"17657806224224","tag_NUM_TYPE":"Tagnumber has correct datatype"}
    {"tagNum":17657806243578,"tag_NUM_TYPE":"Tagnumber is not a STRING. Update the data"}
    {"tagNum":"1765780623244","tag_NUM_TYPE":"Tagnumber has correct datatype"}