Control SQL Query

nbarr007 · Post by **nbarr007** » 03 May 2011 11:59

I have created this query to report the start, end and duration of a group of jobs on a daily basis.

When I run the query I get no output. Could someone please check and advise.

I am running version 6.04

select distinct JOBNAME, schedtab
, to_char(to_timestamp(trim(timestmp)
, 'YYYYMMDDHH24MISS') - numtodsinterval(elaptime / 100
, 'SECOND'), 'YYYY-MM-DD HH24:MI:SS') as start_time
, to_char(to_date(trim(timestmp), 'YYYYMMDDHH24MISS')
, 'YYYY-MM-DD HH24:MI:SS') as end_time
, substr(to_char(numtodsinterval(elaptime / 100, 'SECOND')
, 'HH24:MI'), 12,

as duration
from CMR_RUNINF
where timestmp=to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS')
and schedtab like '%GHOIT%'
order by duration desc

philmalmaison · Post by **philmalmaison** » 04 May 2011 10:40

Hi,
Are you waiting the necessary time for the answer before to stop it (by ctrl-C ?)

Regards,
philmalmaison

nbarr007 · Post by **nbarr007** » 04 May 2011 12:06

Hi Phil

I am using PL/SQL Developer to run the query and all I get is the column headings when I execute, but no data

rgd's

Norman

th_alejandro · Post by **th_alejandro** » 05 May 2011 12:39

DO not use this table, connect to EMUSER database (not CTM). Localize your active net name from :
SELECT active_net_name INTO vactive_netname
FROM emuser.comm ;
with this vactive_netname, localize the tabla X${vactive_netname}JOB and make you select on this table.

gglau · Post by **gglau** » 05 May 2011 6:10

where timestmp=to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS')

There isn't possibly a job with the exact timestamp when the SQL executes. You may want to use < instead of =

philmalmaison · Post by **philmalmaison** » 09 May 2011 4:08

hi,
You can easilly used the ctmruninf

Regards,
Philmalmaison