Data Convert SQL TO JSON
SELECT JSON_OBJECT('EMPLOYEES' :
JSON_ARRAYAGG (
JSON_OBJECT(
'EMPNO' : empno,
'ENAME' : ename,
'JOB' : job
)
)
)
FROM emp));
SELECT
T1.SURVEY_SITE_CODE_ID,
T1.PROJECT_ID,
json_arrayagg (
json_object (
'item_id' value t1.ITEM_ID,
'item_name' value T1.ITEM_NAME,
'meter_instalation_qty' value T2.METER_INSTALATION_QTY,
'close_out_qty' value T2.CLOSE_OUT_QTY
) format json returning clob
) as json_item
FROM (
SELECT A.ID AS SURVEY_SITE_CODE_ID,
A.PROJECT_ID,
C.ITEM_ID,
D.NAME AS ITEM_NAME,
B.company_id AS PROJECT_ORGANIZATION_ID
FROM survey_site_codes A, ---39430 Rows
projects B,
project_organization_item_types C,
ITEMS D
WHERE A.project_id = B.id
AND B.company_id=C.PROJECT_ORGANIZATION_ID
AND C.ITEM_ID=D.ID
AND C.ITEM_TYPE_ID=322) T1,
(SELECT A.SURVEY_SITE_CODE_ID,A.ITEM_ID,B.project_organization_id,
SUM(A.METER_INSTALLATION_QUANTITY) AS meter_instalation_qty,
SUM(C.QUANTITY) AS close_out_qty
FROM
SURVEY_METER_INSTALLATION_PARTS_DETAILS A,
project_organization_item_types B,
CLOSEDOUT_PARTS C
WHERE A.item_id=b.item_id
AND A.SURVEY_SITE_CODE_ID=C.SURVEY_SITE_CODE_ID(+)
AND A.ITEM_ID=C.ITEM_ID(+)
AND A.STATUS=327
AND A.SI_TYPE=105
AND B.ITEM_TYPE_ID=322
GROUP BY A.SURVEY_SITE_CODE_ID,A.ITEM_ID,B.project_organization_id) T2
WHERE T1.SURVEY_SITE_CODE_ID=T2.SURVEY_SITE_CODE_ID(+)
AND T1.ITEM_ID=T2.ITEM_ID(+)
AND T1.PROJECT_ORGANIZATION_ID=T2.PROJECT_ORGANIZATION_ID(+)
GROUP BY T1.SURVEY_SITE_CODE_ID,T1.PROJECT_ID
Comments
Post a Comment