I have a query written in Postgres. This will pick up the records from job_information table with the status as supplied through application (ex : 'READY_TO_RUN') and with limit of records as supplied through application (ex : 100), and then updates the job_information with app given status (ex : 'ACQUIRED') and returns that set (means, returns total job_information table data for those got updated with the given status) of records for application usage.
Can someone give me an advice on the translation in Oracle? Thank you!!
Query in Postgres
UPDATE job_information AS J1
SET status=?
FROM
(SELECT job_name,
job_group,
created_date
FROM job_information
WHERE status =?
AND CURRENT_TIMESTAMP >= scheduled_execution_time
ORDER BY scheduled_execution_time limit ?
) AS J2
WHERE J1.job_name = J2.job_name
AND J1.job_group = J2.job_group
AND J1.created_date = J2.created_date RETURNING *;
Query Example (in postgres):
UPDATE job_information AS J1
SET status= 'ACQUIRED'
FROM
(SELECT job_name,
job_group,
created_date
FROM job_information
WHERE status = 'READY_TO_RUN'
AND CURRENT_TIMESTAMP >= scheduled_execution_time
ORDER BY scheduled_execution_time limit 100
) AS J2
WHERE J1.job_name = J2.job_name
AND J1.job_group = J2.job_group
AND J1.created_date = J2.created_date RETURNING *;
Query I wrote in Oracle SQL - It's not working
UPDATE JOB_INFORMATION SET STATUS=
(
WITH J2 as (
select job_name, job_group, created_date from (SELECT job_name, job_group, created_date FROM job_information WHERE status= :b and current_timestamp >= scheduled_execution_time order by scheduled_execution_time ) where rownum<= :c
)
SELECT distinct :a FROM JOB_INFORMATION J1, J2 WHERE J1.job_name = J2.job_name AND J1.job_group = J2.job_group AND J1.created_date = J2.created_date
)
RETURNING * FROM JOB_INFORMATION BULK COLLECT INTO SOMETHING ;