I have 2 tables as shown below.
Job Table
ID JobId Name StartTime FinishTime ....
01 001 A 2105:12:10 14:00:00 2105:12:10 14:00:10
02 002 A 2105:12:10 14:00:00 2105:12:10 14:00:00
03 003 A 2105:12:10 14:00:00 2105:12:10 14:00:00
04 004 A 2105:12:10 14:00:00 2105:12:10 14:00:00
And
Status Table
ID Status Timestamp JobId
01 Started 2105:12:10 14:00:00 001
02 Step_1_Started 2105:12:10 14:00:00 001
03 Step_1_Finished 2105:12:10 14:00:05 001
04 Step_2_Started 2105:12:10 14:00:05 001
05 Step_2_Finished 2105:12:10 14:00:10 001
06 Finished 2105:12:10 14:00:10 001
07 Started 2105:12:10 14:00:00 002
08 Step_1_Started 2105:12:10 14:00:00 002
09 Step_1_Failed 2105:12:10 14:00:02 002
10 Started 2105:12:10 14:00:00 003
11 Step_1_Started 2105:12:10 14:00:00 003
12 Step_1_Failed 2105:12:10 14:00:02 003
13 Step_1_Canceled 2105:12:10 14:00:04 003
14 Started 2105:12:10 14:00:00 004
15 Step_1_Started 2105:12:10 14:00:00 004
From these 2 tables I have to query for Jobs having states FINISHED, CANCELED, FAILED and ACTIVE.
FINISHED: A Job having status 'Finished'.
CANCELED: A Job having status '%Canceled' and not ('Finished').
FAILED: A Job having status '%Failed' and not '%Canceled' and not 'Finished'.
Active: A Job having status '%Started' and not '%Failed' and not '%Canceled' and not 'Finished'.
I have entities for Job and Status with NamedQueries on Job entity and using TomEE for deployment.
I am using following queries.
FINISHED Jobs between a time interval:
@NamedQuery( name = "findFinishedJobsBetweenTimeIntervals",
query = "SELECT J FROM JobEntity J, IN(J.statuses) JS WHERE " +
"J.startTime >=:timeStampBefore AND J.startTime <:timeStampAfter AND " +
"JS.status = 'Finished'" ),
CANCELED Jobs between a time interval:
@NamedQuery( name = "findCanceledJobsBetweenTimeIntervals",
query = "SELECT J FROM JobEntity J, IN(J.statuses) JS WHERE " +
"J.startTime >=:timeStampBefore AND J.startTime <:timeStampAfter AND " +
"JS.status LIKE '%Failed%' AND JS.status <> 'Finished'" ),
FAILED Jobs between a time interval:
@NamedQuery( name = "findFailedJobsBetweenTimeIntervals",
query = "SELECT J FROM JobEntity J, IN(J.statuses) JS WHERE " +
"J.startTime >=:timeStampBefore AND J.startTime <:timeStampAfter AND " +
"JS.status LIKE '%Failed%' AND JS.status NOT LIKE '%Canceled'" AND JS.status <> 'Finished'" ),
ACTIVE Jobs between a time interval:
@NamedQuery( name = "findActiveJobsBetweenTimeIntervals",
query = "SELECT J FROM JobEntity J, IN(J.statuses) JS WHERE " +
"J.startTime >=:timeStampBefore AND J.startTime <:timeStampAfter AND " +
"JS.status = 'Started' AND JS.status NOT LIKE '%Failed%' AND JS.status NOT LIKE '%Canceled'" AND PS.status <> 'Finished'" ),
With these queries I am getting wrong result for ACTIVE Jobs. A job even with failed or finished state is returned in the active jobs result.
Need help to correct the query.
Thank You.
