I'm trying to create a JSON_OBJECT()
from a scalar subquery like this in Db2 LUW v11.5.4.0:
SELECT
JSON_OBJECT(KEY 'x' VALUE (SELECT 1 FROM sysibm.dual))
FROM sysibm.dual;
This produces
An unexpected token ")" was found following "1 from sysibm.dual)". Expected tokens may include: "<interval_qualifier>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.26.14
A similar query works in Oracle, and I don't see, from the documentation, what I'm doing wrong in Db2. How can I construct a JSON_OBJECT()
from a scalar subquery?
These hacks, and possibly others, seem to work:
SELECT
JSON_OBJECT(KEY 'x' VALUE COALESCE(NULL, (SELECT 1 FROM sysibm.dual)))
FROM sysibm.dual;
SELECT
JSON_OBJECT(KEY 'x' VALUE DECODE(1, 1, (SELECT 1 FROM sysibm.dual)))
FROM sysibm.dual;