Ask Your Question

Is it possible to use the function IIF in LibreOffice Base?

asked 2017-04-28 15:58:17 +0200

Darya gravatar image

when creating a query in SQL, I can not use function IIF as in Access

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2017-04-28 17:48:16 +0200

librebel gravatar image

hello Darya, you could try the CASEWHEN function instead:

CASEWHEN( <boolean value expr>, <value expr 2>, <value expr 3> )

If the [boolean value expr] is true, returns [value expr 2] otherwise returns [value expr 3]. Use a CASE WHEN expression instead for more extensive capabilities and options.

edit flag offensive delete link more

answered 2017-04-28 21:18:21 +0200

EasyTrieve gravatar image

updated 2017-04-28 21:19:22 +0200

Which SQL you can use depends a lot on which database you are connected to. ;-)

The default LO database is HyperSQL 1.8. It doesn't allow many things.

I normally connect to MariaDB (a MySQL clone) which allows many, many things. In MySQL/MariaDB IIF is simply called IF, and you can simply use if(test, iftrue, iffalse) construct.

For links to the various database documentation/reference manuals see my post on this subject here.

(Also I should note that you can use IIF in LO Basic, but that's not SQL.)

edit flag offensive delete link more

answered 2017-04-28 20:51:18 +0200

Ciao, you can use also CASE WHEN expr1 THEN v1[WHEN expr2 THEN v2] [ELSE v4] END

when expr1 is true return v1 [optionally repeated for more cases] [otherwise v4 or null if there is no ELSE]

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-04-28 15:58:17 +0200

Seen: 634 times

Last updated: Apr 28 '17