create a json message
coderoen Jul 31, 2017 7:14 AMHello,
can somebody help me? I want to create a json message, based on an order and 1 or more order details.
BEGIN SELECT JSONOBJECT(northwind.orders.OrderID, northwind.orders.OrderDate, JSONARRAY((SELECT JSONOBJECT(UnitPrice, Quantity) FROM northwind.order_details WHERE northwind.order_details.odr_OrderID = northwind.orders.OrderID)) AS details) AS json_out FROM northwind.orders WHERE northwind.orders.OrderID IN (10248); END
Error message:
org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.api.exception.query.ExpressionEvaluationException: TEIID30328 Unable to evaluate JSONOBJECT(Northwind_datasource.orders.OrderID AS OrderID, Northwind_datasource.orders.OrderDate AS OrderDate , JSONARRAY((SELECT JSONOBJECT(UnitPrice AS UnitPrice, Quantity AS Quantity) FROM northwind.order_details WHERE northwind.order_details.odr_OrderID = northwind.orders.OrderID LIMIT 2)) AS details): TEIID30345 The command of this scalar subquery returned more than one value: SELECT JSONOBJECT(UnitPrice AS UnitPrice, Quantity AS Quantity) FROM northwind.order_details WHERE northwind.order_details.odr_OrderID = northwind.orders.OrderID LIMIT 2
One detail, it works.
{"OrderID":10266,"OrderDate":"1996-07-26","details":[{"UnitPrice":30.4,"Quantity":12}]
When i create a message by hand, it also works.
SELECT JSONOBJECT(northwind.orders.OrderID, northwind.orders.OrderDate, JSONARRAY(JSONOBJECT(ShipAddress, ShipCity) , JSONOBJECT(ShipAddress, ShipCity)) AS addresses) AS json_out FROM northwind.orders WHERE northwind.orders.OrderID IN (10266 );
{"OrderID":10266,"OrderDate":"1996-07-26","addresses":[{"ShipAddress":"Torikatu 38","ShipCity":"Oulu"},{"ShipAddress":"Torikatu 38","ShipCity":"Oulu"}]}
-
requestforhelp.zip 251.8 KB