I have a requirement to convert a SQL Statement into JSON. That's easy enough to do with JSON_ARRAYAGG and JSON_OBJECT, however the query has a cursor in the middle and not sure of the proper syntax for converting cursor data in JSON... Please see query below...
SELECT
JSON_ARRAYAGG(
JSON_OBJECT('pr.ratingid' VALUE pr.ratingid, 'remote_external_rating_id' VALUE prr.paperrefnum,
'STATUS' VALUE pr.status,'patron_id' VALUE pr.ptnid, 'device_id' VALUE d.devid,
'device_asset_number' VALUE d.assetnum,
'device_type_id' VALUE pr.devtypid, 'device_type_description' VALUE dt.devtypname,
'main_game_id' VALUE pr.gameid,
'gaming_day' VALUE TO_CHAR( CAST(pr.dateinserted AS TIMESTAMP WITH TIME ZONE), 'RRRR-MM-DD"T"HH24:MI:SSTZH:TZM' ),
'shift_id' VALUE pr.shiftid, 'date_time_in' VALUE TO_CHAR( CAST(pr.datetimein AS TIMESTAMP WITH TIME ZONE), 'RRRR-MM-DD"T"HH24:MI:SSTZH:TZM' ),
'date_time_out' VALUE TO_CHAR( CAST(pr.datetimeout AS TIMESTAMP WITH TIME ZONE), 'RRRR-MM-DD"T"HH24:MI:SSTZH:TZM' ), 'seat_number' VALUE pr.seatnum ,'time_on_device' VALUE pr.timeondevsec,
'hold_time_seconds' VALUE pr.holdtimesec, 'cash_buy_in' VALUE pr.cashbuyin, 'chips_buy_in' VALUE pr.chipsbuyin, 'marker_buy_in' VALUE pr.markerbuyin, 'cpv_buy_in' VALUE pr.cpvbuyin,
'front_money_buy_in' VALUE pr.fodbuyin, 'cwa_buy_in' VALUE pr.cwabuyin, 'match_buy_in' VALUE pr.matchbuyin, 'promo_buy_in' VALUE pr.promobuyin,
'voucher_buy_in' VALUE pr.titobuyin, 'total_buy_in' VALUE pr.totbuyin, 'chip_walk' VALUE (select NVL(sum(TOTWALKWITH), 0)
from ems.ptnrtgcurrencydetail
where ratingid=pr.ratingid and buyintypid=5),
'cwa_walk' VALUE (select NVL(sum(TOTWALKWITH), 0)
from ems.ptnrtgcurrencydetail where ratingid=pr.ratingid and buyintypid=13),
'voucher_walk' VALUE (select NVL(sum(TOTWALKWITH), 0)
from ems.ptnrtgcurrencydetail where ratingid=pr.ratingid and buyintypid=12),
'total_walk' VALUE pr.totwalkwith,
CURSOR (SELECT prd.gameid FROM ems.ptnratingdetail prd WHERE prd.ratingid = pr.ratingid)
'rated_username' VALUE pr.lastmodby, '
rated_employee_id' VALUE nvl( e.empid, 0 ),
'game_theo_hold_percent' VALUE pr.gametheholdpct,
'average_wager' VALUE pr.avgwager,
'theo_win' VALUE pr.theowin,
'points_awarded' VALUE pr.creditpoint,
'comp_points_awarded' VALUE pr.creditcomppoint
)
)
FROM ems.ptnrating pr, ems.devicetype dt, ems.employee e, ems.device d, ems.ptnratingref prr
WHERE pr.ratingid = P_value
AND dt.devtypid = pr.devtypid
AND e.loginname (+) = pr.lastmodby AND d.devid = pr.devid AND prr.ratingid (+) = pr.ratingid;