Access/jet equivalent of Oracle's decode

Question

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

select
  ...
from
  ...
where
  ..
order by
  decode(status, 2, 0, 1),
  date_column

You can write a custom function in VBA and then call it from your SQL. This may be preferable if you have complex choices, else solutions below Switch or Iif below are best. — heferav, Nov 19 '09 at 13:52

score 6 · Accepted Answer · answered Nov 19 '09 at 14:46

6

The closest analogy is the SWITCH() function e.g.

Oracle:

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 TRUE :)

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 IIF() functions.

answered Nov 19 '09 at 14:46

onedaywhen

55,269
12
100
138

In Access-2010, the IIF function in SQL is *not* short-circuit. Based on both the documentation and empirical testing. – user36800 Sep 05 '15 at 15:23
@user36800 ...but if you have proof that it cannot short circuit then please post somewhere somehow and I pledge to test it :) – onedaywhen Sep 18 '15 at 16:06
The `IIf` function in the Access db engine's implementation does indeed short circuit; VBA `IIf` does not. If @user36800 wants to confirm that point empirically, it's easy. In an Access query, `SELECT IIf(True,2,1/0)` returns 2. In VBA, the same `IIf` expression triggers run-time error 11: *"Division by zero"*. – HansUp Dec 04 '15 at 17:22
I'm running into this problem now, 2 years after my erroneous comment. Yes, empirical testing shows that SQL IIF is short-circuiting (and I'm using SQL 92). It's precisely the behaviour I need right now. – user36800 Sep 01 '17 at 20:36

score 1 · Answer 2 · edited May 23 '17 at 12:23

1

You can try with IIF. See this stackoverflow question.

edited May 23 '17 at 12:23

Community

1
1

answered Nov 19 '09 at 13:22

Pablo Santa Cruz

176,835
32
241
292

score 0 · Answer 3 · answered Nov 19 '09 at 13:36

I think it might compare to switch or choose.

Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])

-- http://office.microsoft.com/en-us/access/HA012289181033.aspx

Choose(index, choice-1[, choice-2, ... [, choice-n]])

-- http://msdn.microsoft.com/en-us/library/aa262690%28VS.60%29.aspx

score 0 · Answer 4 · edited Apr 27 '16 at 14:14

0

You can use the SWITCH function:

LABEL: Switch(
   [TABLE_NAME]![COL_NAME]='VAL1';'NEW_VAL1';
   [TABLE_NAME]![COL_NAME]='VAL2';'NEW_VAL2';
)

Note semicolons and not commas.

The example above works in queries in MS Access 2010.

edited Apr 27 '16 at 14:14

Paul Roub

36,322
27
84
93

answered Apr 27 '16 at 14:11

elrado

4,960
1
17
15

Access/jet equivalent of Oracle's decode

4 Answers4

Linked