Is there an equivalent for Oracle's decode() in Access (or Jet, for that matter).
The problem I am facing is: I should sort (order) a resultset based basically upon
a status and a date (with all records having status = 2) at the end.
In Oracle I'd go something like
decode(status, 2, 0, 1),
The closest analogy is the
SWITCH() function e.g.
SELECT supplier_name, decode(supplier_id, 10000, 'IBM', 10001, 'Microsoft', 10002, 'Hewlett Packard', 'Gateway') result FROM suppliers;
Access Database Engine
SELECT supplier_name, SWITCH(supplier_id = 10000, 'IBM', supplier_id = 10001, 'Microsoft', supplier_id = 10002, 'Hewlett Packard', TRUE, 'Gateway') AS result FROM suppliers;
Note that with the
SWITCH() function you have to supply the full predicate each time, so you are not restricted to using just supplier_id. For the default value, use a predicate that is obvious to the human reader that it is TRUE e.g.
1 = 1 or indeed simply
Something that may not be obvious is that the logic in the
SWITCH() function doesn't short circuit, meaning that every expression in the function must be able to be evaluated without error. If you require logic to short circuit then you will need to use nested