0

i am using libreoffice base (new version) with HSQLDB and want to do a query where in a certain column when that column or cell data is not empty i want the resulting cell output to be "X", otherwise it should stay empty. how can i do that with HSQLDB syntax?

i googled but actually could not find anything in this regard.

Rolf Fleckenstein
  • 11
  • 1
  • `COALESCE(col, 'X')`? – The Impaler Jun 10 '20 at 18:09
  • 1
    Whoever downvoted, please add an explanation as to what is wrong with the question and how it could be improved. Regarding `COALESCE`, it looks like that does the opposite of what is asked - displays 'X' when the data is empty. – Jim K Jun 10 '20 at 18:41

1 Answers1

1

Tested in HSQLDB 1.8.

SELECT B.NAME
FROM "Table1" A
LEFT OUTER JOIN
    (SELECT ID, 'X' AS NAME FROM "Table1") B
ON A.ID = B.ID AND A.NAME <> ''

Table1:

ID NAME
~~ ~~~~
1  George
2  Sally
3

Result: 

X
X
(empty)

EDIT:

HSQLDB does have CASE WHEN. Documentation: http://www.hsqldb.org/doc/1.8/guide/ch09.html#N1251E.

SELECT CASE WHEN NAME<>'' THEN 'X' ELSE '' END FROM "Table1"

SELECT CASEWHEN(NAME<>'', 'X','') FROM "Table1"
Jim K
  • 12,824
  • 2
  • 22
  • 51