Do it the same way
select job_name, status, INTCODES.TEXT -- << HERE
TO_DATE('12/31/1969 20:00:00','MM/DD/YYYY HH24:MI:SS') +
startime /60 /60 /24 as STARTIME,
TO_DATE('12/31/1969 20:00:00','MM/DD/YYYY HH24:MI:SS') +
endtime /60 /60 /24 as ENDTIME,
runtime, avg_runtime
from INTCODES, job, job_runs, avg_job_runs -- << HERE
where job.job_name like 'ISIS%'
and job.joid = job_runs.joid
and job.joid = avg_job_runs.joid
AND INTCODES.FLD = 'status' -- << HERE
AND INTCODES.CODE = JOB_RUNS.STATUS -- << HERE
and TO_DATE('12/31/1969 20:00:00','MM/DD/YYYY HH24:MI:SS') +
startime /60 /60 /24 > SYSDATE - 1
order by startime;
Stephen Weston
---------------------------------------
Looking to do is get the following. This is the query that I have that
works. The only thing is that it displays the status code and not the
status text. How do I get the status text to be displayed instead of the
status code with the below query. What you have works, but how would I
add that to the query below?
alter session set nls_date_format = "MM/DD/YYYY hh24:mi:ss";
select job_name, status,
TO_DATE('12/31/1969 20:00:00','MM/DD/YYYY HH24:MI:SS') +
startime /60 /60 /24 as STARTIME,
TO_DATE('12/31/1969 20:00:00','MM/DD/YYYY HH24:MI:SS') +
endtime /60 /60 /24 as ENDTIME,
runtime, avg_runtime
from job, job_runs, avg_job_runs
where job.job_name like 'ISIS%'
and job.joid = job_runs.joid
and job.joid = avg_job_runs.joid
and TO_DATE('12/31/1969 20:00:00','MM/DD/YYYY HH24:MI:SS') +
startime /60 /60 /24 > SYSDATE - 1
order by startime;
-----Original Message-----
From: Weston, Stephen
Sent: 29 September 2006 14:49
To: 'Chad GARRISON '; LazyDBA Discussion
Subject: RE: Query Help
select i.text
from intcodes i
, job_runs j
where i.fld = 'status'
and i.code = j.status ;
Stephen Weston
-----Original Message-----
From: Chad GARRISON
[mailto:oracledba-ezmlmshield-x66759319.[Email address protected]
Sent: 29 September 2006 14:34
To: LazyDBA Discussion
Subject: Query Help
I am trying to do the following in a query.
When I query the status from the job_runs table it displays the status
in code format. But I want to display it in text format. There is a
table (intcodes) that defines what the codes equal. How do I tie the 2
tables together to display the status in text form not in code form
within the query.
SQL> select status
2 from job_runs;
STATUS
----------
4
4
4
4
4
4
4
4
4
4
SQL> select *
2 from intcodes;
FLD CODE TEXT
------------------------------ ---------- -----------
status 0
status 1 RUNNING
status 3 STARTING
status 4 SUCCESS
status 5 FAILURE
status 6 TERMINATED
status 7 ON_ICE
status 8 INACTIVE
status 9 ACTIVATED
status 10 RESTART
status 11 ON_HOLD
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Oracle LazyDBA home page