select max

The sql statement is supposed to return the run and sequence that were not validated

constraints: the run has to to be of ALLBEAM run_type and has to have a status of 0

(select count( le.action) naction, rs.run, rs.sequence from phoffline.runseq_numbers rsn,phoffline.runs r,phoffline.log_entries le, phoffline.runseqs rs where rs.run=rsn.run and
rs.sequence=rsn.sequence and rs.seq_date>sysdate-1 and rs.status=0 and r.run=rs.run and r.run_type='ALLBEAM' and le.action(+)='DV_PLOT_SEQ' and le.logid(+)=rsn.logid
group by rs.run,rs.sequence) where naction=0

The second sql statement is to get the run and sequences which are ready to be validated: (ready is defined to have a status that is =0, run_type='ALLBEAM' , the file has to be sunk, and pass0 job has to be terminated

select dv.run, dv.sequence from (select count( le.action) naction, rs.run, rs.sequence from phoffline.runseq_numbers rsn,phoffline.runs r,phoffline.log_entries le,
phoffline.runseqs rs where rs.run=rsn.run and
rs.sequence=rsn.sequence and rs.seq_date>sysdate-1 and rs.status=0 and r.run=rs.run and r.run_type='ALLBEAM'
and le.action(+)='DV_PLOT_SEQ' and le.logid(+)=rsn.logid
group by rs.run,rs.sequence) dv,
(select count( le.action) countsink, rs.run, rs.sequence from phoffline.runseq_numbers rsn,phoffline.runs r,phoffline.log_entries le,
phoffline.runseqs rs where rs.run=rsn.run and
rs.sequence=rsn.sequence and rs.seq_date>sysdate-1 and rs.status=0 and r.run=rs.run and r.run_type='ALLBEAM'
and le.action(+)='SINK_END_SEQ' and le.logid(+)=rsn.logid
group by rs.run,rs.sequence) sink,
(select count( le.action) countpass, rs.run, rs.sequence from phoffline.runseq_numbers rsn,phoffline.runs r,phoffline.log_entries le,
phoffline.runseqs rs where rs.run=rsn.run and
rs.sequence=rsn.sequence and rs.seq_date>sysdate-1 and rs.status=0 and r.run=rs.run and r.run_type='ALLBEAM'
and le.action(+)='P0_END_SEQ' and le.logid(+)=rsn.logid
group by rs.run,rs.sequence) pass
where dv.naction=0 and pass.countpass>0 and sink.countsink>0 and dv.run=pass.run and pass.run=sink.run
and sink.sequence=pass.sequence and pass.sequence=dv.sequence
group by dv.run, dv.sequence

The following sql statement displays the number of times SINK_END_SEQ, P0_END_SEQ, DV_PLOT_SEQ actions were done (in that order), with the constraints( status='0' and run_type='ALLBEAM')

select max(ta1.run) run,max(ta1.sequence) sequence,count(rl.action) naction
from
(select rn.run,rn.sequence,ruu.run_type,ta.action,le.log_date
from phoffline.log_entries le, phoffline.runseq_numbers rn, phoffline.runs ruu, phoffline.runseqs ru,(select action from phoffline.log_actions where (action= 'DV_PLOT_SEQ' or action='P0_END_SEQ' or action='SINK_END_SEQ') ) ta
where ta.action=le.action and rn.logid=le.logid and rn.run=ru.run and rn.run=ruu.run and ru.sequence=rn.sequence
and ruu.run_type='ALLBEAM' and ruu.run between 7000 and 7050) rl,
(select r.run,r.sequence,l.action,l.action_order,rr.run_type from (select action from phoffline.log_actions where (action= 'DV_PLOT_SEQ' or action='P0_END_SEQ' or action='SINK_END_SEQ') ) ta ,
phoffline.log_actions l,phoffline.runseqs r,phoffline.runs rr
where rr.run_type='ALLBEAM' and l.action=ta.action and r.run=rr.run
and r.run between 7000 and 7050) ta1
where ta1.action=rl.action(+) and ta1.run=rl.run(+) and ta1.sequence=rl.sequence(+)
group by ta1.run,ta1.sequence,ta1.action
order by ta1.run desc,ta1.sequence,max(ta1.action_order)

the output is something like:

run	seq	nactions
7000	0	1(SINK_END_SEQ
7000	0	2(P0_END_SEQ)
7000	0	0(DV_PLOT_SEQ)

this sql statement displays the run and sequence numbers which P0 jobs are finished, and DV_PLOT jobs are not done, and have constraints( status=0, run_type=allbeam)