hi,
I am working on a SQL query in LabKey with a CASE statement that returns a Boolean value, but I am getting an error ("exceptionClass" : "org.labkey.query.sql.Query$QueryInternalException").
SELECT
CASE
WHEN Message.gender = 'M' then true
ELSE false
END as booleanTest
FROM Message
WHERE Message.messageId = '12345';
Also, I have tried setting the Boolean values to 1 and 0, which enables the query to execute but then I end up with NaN errors when JavaScript code expects true/false.
Is it possible to fix the SQL or should I just refactor my JS code?
Many thanks,
bront |
| |
| jeckels responded: |
2017-03-05 16:59 |
Hi Bront,
Which database are you using?
Have you tried using 0 and 1 with a CAST and BIT?
Thanks,
Josh |
| |
| bront responded: |
2017-03-06 07:26 |
Hi Josh,
I am using PostgreSQL 9.3.
I tried the following, but I'm still getting the "internal error while parsing." I am not sure I am formatting the CAST correctly.
SELECT
CASE
WHEN Message.gender = 'M' then CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END as booleanTest
FROM Message
WHERE Message.messageId = '12345';
Thanks,
bront |
| |
| Jon (LabKey DevOps) responded: |
2017-03-07 01:14 |
Hi Bront,
I think it is supposed to look like this:
SELECT
CAST (
CASE
WHEN Message.gender = 'M' then 1 ELSE 0
END
AS BIT)
as booleanTest
FROM Message
WHERE Message.messageId = '12345';
Since the use of CAST is basically "CAST (expression AS type)", then the CASE would have to be an expression here.
Can you give the above a try?
Regards,
Jon |
| |
|
|