Case (IF/THEN/ELSE) Statements – roommaster

SQL allows "CASE" statements to allow "IF/THEN/ELSE" logic. If the search-condition following the WHEN statement is satisfied, the expression following the THEN statement is returned. Otherwise the expression following the ELSE statement is returned, if it exists.

CASE expression WHEN expression THEN expression, ... [ ELSE expression ] END

This allows you to convert specific results into words or phrases that are easier to read on a report.

Using SQL Wizard

In the SQL Wizard, the CASE statements are found on the Change Field Values tab under Format Column. In the Define more criteria section, enter the expected result in the If equals... field and what you want displayed in the Convert to.. field in the same section. Then click add to save the conversion. More than one result can be converted for each field column. If no converted result is returned, in the Else section, you can type what you want displayed in the Convert to... field. If left blank, no result will appear blank on the report.

For example, to have the room statuses appear on a report as full words rather than a single letter, in the If equals... field type in "V" and in the Convert to... field Type in "Vacant". Click add. Repeat with "O" for "Occupied", "D" for "Dirty". Type "Unavailable" in the Convert to... in the Else section and all rooms not currently vacant, dirty, or occupied will appear as unavailable on the report.

Using Free-Form SQL Syntax

In this example, instead of showing "V", "O", "U" and "D" in the results, we can show "Vacant", "Occupied", "Unavailable" and "Dirty".

SELECT ROOMS.ROOMNUMBER AS 'ROOM',

 CASE ROOMSTATUS WHEN 'V' THEN 'VACANT'

         WHEN 'O' THEN 'OCCUPIED'

         WHEN 'D' THEN 'DIRTY'

         WHEN 'U' THEN 'UNAVAILABLE' END as 'STATUS',

 CASE WHEN CHECKOUT = CURRENT DATE then 'C/OUT'

         WHEN CHECKOUT > CURRENT DATE then 'STAY'

         ELSE   ' '  END as 'C/O'

 FROM ROOMS,FOLIOHD

         WHERE ROOMS.FOLIO *= FOLIOHD.NUMBER

 ORDER BY ROOMS.ROOMNUMBER

Note that MariaDB SQL requires an "END" statement at the end of all CASE statements, where some SQL/92 systems do not.