1 2 Previous Next 16 Replies Latest reply on May 23, 2019 3:04 PM by Andy Ba

    Use of  JSONOPBJECT and JSONARRAY_AGG (TEIID30345 The command of this scalar subquery returned more than one value)

    Andy Ba Newbie

      I have a request to show Parent / Child information that could have multiple rows in the Parent tables and multiple rows in the child tables within a subquery.

       

       

      I attempted the following use of JSONOPBJECT and JSONARRAY_AGG, but I am getting an error in the subquery.

       

       

      TEIID30345 The command of this scalar subquery returned more than one value

       

       

      can you please see what needs to be changed to allow a JSON response the allows for main customer data and drill down customer information with multiple rows in a subquery

       

       

      BEGIN

      SELECT JSONOBJECT( WO.main_cust_data.key_id_data,

      WO.main_cust_data.CName,

      WO.main_cust_data.CDate,

      WO.main_cust_data.CseqNum,

      JSONARRAY_AGG((SELECT JSONOBJECT(WO.cust_drill_down.COutDate,

      WO.cust_drill_down.amount,

      WO.cust_drill_down.CDate,

      WO.cust_drill_down.key_id_data,

      WO.cust_drill_down.CseqNum)

      FROM WO.cust_drill_down

      WHERE (WO.cust_drill_down.key_id_data = WO.main_cust_data.key_id_data)

      AND (WO.cust_drill_down.CDate = WO.main_cust_data.CDate)

      AND (WO.cust_drill_down.CseqNum = WO.main_cust_data.CseqNum))) AS resultDataSet) AS json_out

      FROM WO.main_cust_data

      WHERE WO.main_cust_data.mid = (WO.getwriteoffinfo.p_key_id_data)

      GROUP BY WO.main_cust_data.key_id_data, WO.main_cust_data.CName,WO.main_cust_data.CDate, WO.main_cust_data.CseqNum;

      END

        1 2 Previous Next