LibreOffice Base V5.3.2.2 - Equivalent to Access IIF

Question

I'm new to Base and trying to convert a simple MS Access query:

Box:IIF([Archive_Loc] like '**ox*','B')

I'm using casewhen("Archive_Loc" = '%ox%','B')

but I can't seem to figure out how if I can use LIKE instead of = which obviously doesn't give me the result I'm after of returning B if Archive_Loc contains the phrase ox.

Any advice would be appreciated - Thanks

Answer 1

The answer to your question depends on the specific SQL flavor of the database being used. In other words, different databases use somewhat different SQL syntaxes. So the first thing to ask you is which database are you using???

Here are some options: HSQLDB 1.8 is the default built in database. (Note: there is a 2.3 version but by default Base does not use it.) You can also choose Firebird. As for me, most of the time I prefer to connect to MariaDB (a MySQL free db engine) because it has other valuable tools available like HeidiSQL and phpmyadmin.

Once you know which database you are using, then here is a link to a list of links for various database documentation that I put together some time ago as I was first learning Base: https://ask.libreoffice.org/en/question/80972/to-learn-libreoffice-base-are-there-introductions-or-tutorials/

Please scroll down to "IMPORTANT DATABASE DOCUMENTATION".

And not only should this help you answer this specific question, but it will hopefully provide you with a valuable resource to answer many more questions yourself.

The HSLQDB 1.8 manual is a good read, although hard to find a link to, and I'm guessing this is probably the DB you are using.

Hope this helps you.

Answer 2

You seem to have 2 questions:

1. IIF syntax help
2. Like with CASEWHEN() function.

Firstly you do have SQL operations available, but you seem to be doing text functions.

Also you should have a Filter Navigator wizard that might help.

A1. syntax would be:

IF([boolean_field];"Yes";"No")

A2. Like syntax should be should be:

IF([Archive_Loc] like '%ox%','B') --will show something containing "ox" in it.

rule:

For text, written in quotation marks (' '); "_" for a variable character, "%" for an arbitrary number of variable characters

Referenced from website download from: http://www.libreoffice.org/get-help/documentation/

Here is there online help site for BASE: https://help.libreoffice.org/Common/Database_1

Answer 3

I solved my problem in the end by connecting base to my existing mysql database and setting the queries to run the sql directly so that it bypassed the parser.

Job done :-)