net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: ELSE

[logineimer]

Hello,

I have a problem with this select:

SELECT
2 AS PRIO,
2 AS SEVERITY,
1 AS ERROR,
cstr(Person) AS PERSON,
Name AS NAME,
UserName AS UserName,
'1337' AS Typ,
Field1 + Field2 + Field3 AS ConcatUser,
Field1,
Field2,
Field3,
Datum,
cstr(Pers) as Pers,
Name,
CheckNumber,
cstr(Action) as Action,
Info,
SWITCH(
(Field1 + Field2 + Field3) = '000000' and cstr(Action) = '00000',
'A000000A',
true, UserName) AS SuperUserName,
Creator
from [TestTable] tbl
where tbl.tdate <= date() -6 AND tbl.tdate >= date() -59

UCanAccess throws the following Exception:
net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: ELSE
[...]
Caused by: org.hsqldb.HsqlException: unexpected token: ELSE
[...]

I'm using UCanAccess 2.0.9.4. Changelog says, there are fixes regarding SWITCH statement.. but there is this error.

Greetings!

[logineimer]

Hello!

I have a problem with this select:

SELECT
2 AS PRIO,
2 AS SEVERITY,
1 AS ERROR,
cstr(Person) AS PERSON,
Name AS NAME,
UserName AS UserName,
'1337' AS Typ,
Field1 + Field2 + Field3 AS ConcatUser,
Field1,
Field2,
Field3,
Datum,
cstr(Pers) as Pers,
Name,
CheckNumber,
cstr(Action) as Action,
Info,
SWITCH(
(Field1 + Field2 + Field3) = '000000' and cstr(Action) = '00000',
'A000000A',
true, UserName) AS SuperUserName,
Creator
from [TestTable] tbl
where tbl.tdate <= date() -6 AND tbl.tdate >= date() -59

The select results in the following error:

net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: ELSE
at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:506)
[...]
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: ELSE
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:504)
... 53 more
Caused by: org.hsqldb.HsqlException: unexpected token: ELSE</init>

I'm using UCanAccess 2.0.9.4 which says SWITCH statement is fixed..

Greetings!

[Marco Amadei]

My pre-processing approach for this specific variable number arguments function seems to be very weak and it needs to be changed.
In the meantime you can easily get it working using this format:
select SWITCH(Field1+field2+field3 = '000000' and Action = 0,'A000000A',true, UserName) from t;

[logineimer]

Thanky you! I don't know how this could happen but I accidentally pasted the wrong select. Here is the right one:

SELECT
2 AS PRIO,
2 AS SEVERITY,
1 AS ERROR,
cstr(Person) AS PERSON,
Name AS NAME,
UserName AS UserName,
'1337' AS Typ,
Field1 + Field2 + Field3 AS ConcatUser,
Field1,
Field2,
Field3,
Datum,
cstr(Pers) as Pers,
Name,
CheckNumber,
cstr(Action) as Action,
Info,
SWITCH(
(Field1 + Field2 + Field3) = '000000' and cstr(Action) = '00000',
'A000000A',
(Field1 + Field2 + Field3) = '121212' and cstr(Action) = '22222',
'A111111A',
(Field1 + Field2 + Field3) = '232323' and cstr(Action) = '11111',
'A222222A',
true, UserName) AS SuperUserName,
Creator
from [TestTable] tbl
where tbl.tdate <= date() -6 AND tbl.tdate >= date() -59

You see it's mostly the same but there are more arguments in the SWITCH statement. Can I solve this, too?

[Marco Amadei]

Like above, try:
SWITCH(
Field1 + Field2 + Field3 = '000000' and Action = 0,
'A000000A',
Field1 + Field2 + Field3 = '121212' and Action = 22222,
'A111111A',
Field1 + Field2 + Field3 = '232323' and Action = 11111,
'A222222A',
true, UserName) AS SuperUserName

Otherwise you can use directly the "case when" syntax as shown here:
http://www.hsqldb.org/doc/1.8/guide/ch09.html

[logineimer]

That works! Thanks a lot!