we are using Oracle v12+
we have a situation where we need to update status
column to previous value whenever we have first RENEWAL
for every code
(it is functional ID and has many rows against one ID) otherwise ADD
.
See sample i/o below. Data is sorted by timestamp for each ID.
Do we need specific joins to update? I have copied data to temp table for that but not getting success.
Table name: table_book_status
Input CLOB
[code] [word] [status] [timestamp]
B000JMLBHU book {"name" : "Kunal", "type" : "RENEWAL"}
B000JMLBHU read {"name" : "Kunal", "type" : "RENEWAL"}
B000JMLBHU was {"name" : "Kunal", "type" : "MODIFY"}
B000JMLBHU story {"name" : "Kunal", "type" : "ADD"}
B000R93D4Y with {"name" : "RAHUL", "type" : "RENEWAL"}
B000R93D4Y book {"name" : "RAHUL", "type" : "RENEWAL"}
B000R93D4Y story {"name" : "RAHUL", "type" : "RENEWAL"}
B000R93D4Y was {"name" : "RAHUL", "type" : "MODIFY"}
B000R93D4Y have {"name" : "RAHUL", "type" : "ADD"}
B001892DGG was {"name" : "Kanav", "type" : "ADD"}
B001892DWA was {"name" : "Kavita", "type" : "ADD"}
B001BXNQ2O was {"name" : "Keshav", "type" : "RENEWAL"}
B001BXNQ2O book {"name" : "Keshav", "type" : "RENEWAL"}
B001H55R8M was {"name" : "Raghav", "type" : "MODIFY"}
B001HQHCBQ was {"name" : "RINA", "type" : "ADD"}
B001HQHCBQ story {"name" : "RINA", "type" : "ADD"}
B001HQHCBQ bella {"name" : "RINA", "type" : "ADD"}
B001HQHCBQ with {"name" : "RINA", "type" : "ADD"}
B001HQHCBQ love {"name" : "RINA", "type" : "ADD"}
B001HQHCBQ zsadist {"name" : "RINA", "type" : "ADD"}
Output
[code] [word] [status] [timestamp]
B000JMLBHU book {"name" : "Kunal", "type" : "RENEWAL"}
B000JMLBHU read {"name" : "Kunal", "type" : "**MODIFY**"}
B000JMLBHU was {"name" : "Kunal", "type" : "MODIFY"}
B000JMLBHU story {"name" : "Kunal", "type" : "ADD"}
B000R93D4Y with {"name" : "RAHUL", "type" : "RENEWAL"}
B000R93D4Y book {"name" : "RAHUL", "type" : "RENEWAL"}
B000R93D4Y story {"name" : "RAHUL", "type" : "**MODIFY**"}
B000R93D4Y was {"name" : "RAHUL", "type" : "MODIFY"}
B000R93D4Y have {"name" : "RAHUL", "type" : "ADD"}
B001892DGG was {"name" : "Kanav", "type" : "ADD"}
B001892DWA was {"name" : "Kavita", "type" : "ADD"}
B001BXNQ2O was {"name" : "Keshav", "type" : "RENEWAL"}
B001BXNQ2O book {"name" : "Keshav", "type" : "**ADD**"}
B001H55R8M was {"name" : "Raghav", "type" : "MODIFY"}
B001HQHCBQ was {"name" : "RINA", "type" : "ADD"}
B001HQHCBQ story {"name" : "RINA", "type" : "ADD"}
B001HQHCBQ bella {"name" : "RINA", "type" : "ADD"}
B001HQHCBQ with {"name" : "RINA", "type" : "ADD"}
B001HQHCBQ love {"name" : "RINA", "type" : "ADD"}
B001HQHCBQ zsadist {"name" : "RINA", "type" : "ADD"}
With the help of stackOverflow community we were able to create a select query
SELECT code,
status,
CASE
WHEN sm = 1
AND status = 'RENEWAL'
THEN COALESCE(lgst, 'ADD')
ELSE status
END AS status1,
timestamp
FROM
(SELECT code,
JSON_VALUE(status, '$.type') AS status,
SUM(
CASE
WHEN JSON_VALUE(status, '$.type') = 'RENEWAL'
THEN 1
ELSE 0
END) over (partition BY code order by timestamp) AS sm,
lag(JSON_VALUE(status, '$.type')) over (partition BY code order by timestamp) AS lgst,
timestamp
FROM table_book_status
);
This is giving perfect result as needed but in select view, we want to get that converted to UPDATE
oracle query or maybe PL/SQL
. Any suggestion on that.