[Solved] CASEWHEN and OR

[Nocton]

I have a table with fields ConvenorID1 and ConvenorID2. When either has a value, I wish to use a query to create a new field Both containing the word 'Convenor'. I have made a new field in the query with the statement:
CASEWHEN( "ConvenorID1", 'Convenor', '' ) which works fine as does CASEWHEN( "ConvenorID2", 'Convenor', '' ) . However, I have not been able to extend it to have both conditions at once, i.e. the logical condition 'ConvenorID1 or ConvenorID2'. Thus the statement:
CASEWHEN( "ConvenorID1" OR "ConvenorID2" ), 'Convenor', '' ) gives an error, as do all variants I have tried including OR("ConvenorID1";"ConvenorID2")
What is the correct syntax please?

Regards

Nocton

[RPG]

Hello

You have a bug in the syntax

CASEWHEN( "ConvenorID1" OR "ConvenorID2" ), 'Convenor', '' )

I think use this

Code: Select all

CASEWHEN( "ConvenorID1" OR "ConvenorID2" , 'Convenor', '' )

Romke

[Nocton]

Sorry, that extra ) was a typo. Your version is the one I am using and it gives an error message:
"The column CASEWHEN( "ConvenorID1" OR "ConvenorID2" , 'Convenor', '' ) is unknown".

[RPG]

Hello

I think you give to little information for an answer.
What is the complete SQL. And what are the column names in your table.

You spelled the column name wrong as far I know now.

Romke

[Nocton]

In fact I have solved my problem with the workaround:
CASEWHEN( CONCAT( "ConvenorID1", "ConvenorID2" ) > 0, 'Convenor', '' )

But I should still like to know why the OR statement does not work.

Regards,

Nocton

[rudolfo]

The OR is not working because the language is SQL and not C or Javascript or perl or python, ...
In SQL you need two expressions that both return a boolean value (true, false, well or null) on both sides of the OR operator. ConvenorID1 and ConvenorID2 are probably string type if you concatenate them, so they return CHAR or VARCHAR, but not Boolean.

[Villeroy]

Code: Select all

CASEWHEN( (NOT "ConvenorID1" IS NULL) OR (NOT "ConvenorID2" IS NULL) , 'Convenor', '' ) AS "Some Alias"

[Nocton]

No, thank you, Villeroy. That does not work either. I already tried with IS NULL.
The fields are numerical/integers.

[Villeroy]

Indeed, it does not work with the Base interpreter. In direct mode it works as it should.
This is another variant that runs as documented in direct mode only (embedded HSQLDB 1.8):

Code: Select all

select *,case when (NOT X IS NULL)OR(NOT Y IS NULL) then 'Yes' else 'No' end AS "Blah" FROM "Table1"

All those little helpers in Base are broken beyond repair. At least they do not get it right after 6 years.

[Nocton]

Thanks, again. It is good to know where the problem lies. I've marked it 'Solved'.
As I have got something that works, I'll stick with it. But it does make development frustrating!

[RPG]

Hello

I think when you switch to native mode then you can work with OR.

When you work in the query designer then there is a button with SQL. When you click on this button you switch of OOo-mode to native mode or back.

The SQL button only activ when you are not in the GUI but only in the text mode .

Romke

[Villeroy]

Hello

I think when you switch to native mode then you can work with OR.

When you work in the query designer then there is a button with SQL. When you click on this button you switch of OOo-mode to native mode or back.

The SQL button only activ when you are not in the GUI but only in the text mode .

Romke

Direct SQL allows to file the request directly to the underlying database application (HSQLDB, MySQL, whatever). Base does not care about syntax nor features. The SQL may be very different from what the graphical SQL designer can interprete so it makes no sense to design these database specific SQL statements in graphical mode.
This way we can circumvent many of Base's bugs and restrictions. Unfortunately we never get editable row sets from direct SQL. Even the most simple SELECT * FROM "Table" returns a read-only set from an editable table when run in direct mode.