Using MySQL.
I need to conditionally assign a string value from a numeric field value. By example
SELECT IF(`myfield` = 1,'YES','NO') AS response FROM mytable
What’s the solution in Base?
Thanks
Hello,
Works without a problem with MySQL 8.s in Ubuntu 20.4 with:
Version: 7.3.0.3 / LibreOffice Community
Build ID: 0f246aa12d0eee4a0f7adcefbf7c878fc2238db3
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded
See → MySQL IF() Function
Used your SQL with my table and field name. Tested with Decimal, Bit and Integer fields. All OK.
When I try it in Base query SQL, I get “SQL Syntax Error”
That may be down to me not properly understanding the Base SQL syntax:
For example, following the query syntax in BASE, I have
IF("banner_tracks"."track_type" = 1, 'Clicks','Impressions') "TrackType"
which fails.
In mysql I’d do
…
IF(banner_tracks.track_type = 1, ‘Clicks’,‘Impressions’) AS TrackType
…
which works fine in MYSQL, but fails with a syntax error in Base.
Turn on Run SQL directly:
Also on Edit menu.
.
Edit:
This works without Run SQL Directly..:
What you may not understand is that the Query interpreter was written for HSQLDB v1.8 long ago. So if using SQL syntax that is different because of a different DB being used, you will get an error because it is not recognized. Turning on the Run Directly bypasses the interpreter.
If not working in normal query and works in MySQL, see no reason it would not work in Query with Direct Mode turned on.
.
.
Cannot determine this cause without further information.
There is also the case statement:
SELECT ( CASE
WHEN (`myfield` = 1) THEN 'YES'
ELSE 'NO' END; )
AS response FROM mytable
And as noted in the first answer: There is no solution in Base. Base only connects to different databases, and available solutions are limited by the used database.
As already mentioned, direct SQL mode uses the database’s SQL syntax, MySQL in this particular case. The opposite mode is the “parsed mode” which uses a Base specific syntax and passes a precompiled query to the database driver.
Only the parsed mode returns editable row sets, allows named parameters and subform connections. All these things do not work in direct SQL mode.
One thing I do not fully understand is which functions are availlable in which mode. Can we use the CASE WHEN THEN END with MySQL while in parsed mode? Can we use MySQL’s IF() function while in parsed mode? From my experience with H2 and external HSQL2 via JDBC, I would say that all(?) built-in functions of the database engine are accessible in parsed mode.
I would expect that
SELECT IF(`myfield` = 1,'YES','NO') AS response FROM mytable
with properly set backticks should work in direct mode (MySQL syntax)
whereas
SELECT "mytable".*, IF("myfield" = 1,'YES','NO') AS "response" FROM "mytable" WHERE "XID" = :param_XID
should work in parsed mode with all fields “mytable”.* editable, including the named parameter and in subform context as well.
HOWEVER:
IMHO, there is no need to use IF(myfield = 1,‘YES’,‘NO’) because the user interface (forms and reports) can easily display 1 and 0 or True/False as “Yes” and “No” by means of formatting attributes.
Thanks for that detail. I sort of figured that Base was stuck with some SQL legacy issues.
The report forms are impractical. but I can use the same YES/ NO approach you suggest in the calc sheet.
Cheers
Have no clear answer (specifically for MySQL v8.x server) as to what requires Direct mode needs to be on for. Case in MySQL is lower → Identifier Case Sensitivity.
.
Your above statement works with Direct mode on or off. For the data field myfield, it works in parsed mode with quotes or back ticks or none surrounding, or all upper case or all lower case or mixed case. Table names are different and depend upon the setting in MySQL (see linked document).
.
Interestingly enough, it is not always apparent what is the situation.
.
This works in parsed mode:
SELECT IF(`myfield` = 1,'YES','NO') AS response FROM mytable
But this does not and need direct mode on:
SELECT IF(`myfield` = 1,'YES','NO') AS response FROM mytable
.
So you say they are identical. Appears so but the problem is another situation; the query has the same name as the table. This is allowed in MySQL and the statement works with Direct mode on. With HSQLDB embedded you will get an error trying to name the query with the same name as a table.
.
My practice is to run a statement normally. If Error try Direct. If still error look for problem. Only takes a few seconds to test with direct on. Preferable to run with it off but have had to turn it on even with HSQLDB embedded (believe it had to do with either joins or unions).
With Direct mode on, it typically presents a more descriptive message when there is an error.