Results 1 to 10 of 10
  1. #1
    Join Date
    May 2008
    Location
    Atlanta, GA
    Posts
    14

    Unanswered: combining select statements for a Report

    I am a Autosys consultant and I wish to create a report that tells me the number of job executions per status from the proc_event table for a specific day.

    I have used the following statements:
    1. Total number of Job executions (event object IDs) for May 14, 2008
    SQL> select count(*) eoid from proc_event where que_status_stamp like '14-MAY-08';

    EOID
    ----------
    24763

    2. The various status of all Job executions for May 14, 2008
    select distinct proc_event.status,intcodes.text from proc_event,intcodes where que_status_stamp like '14-MAY-08' and proc_event.status=intcodes.code;
    STATUS TEXT
    ---------- ----------
    0
    1 RUNNING
    3 STARTING
    4 SUCCESS
    5 FAILURE
    3. The number of job execution per specific status
    SQL> select count(*) eoid from proc_event where que_status_stamp like '14-MAY-08' and status=0;

    EOID
    ----------
    6138

    Is there a way for me to combine the above statement to retrieve the output as follows?

    STATUS TEXT Job Count
    ---------- ---------- -----------
    0 6138
    1 RUNNING 6223
    3 STARTING 42
    4 SUCCESS 6169
    5 FAILURE

    Thanks,
    Anthony

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try:
    Code:
    select intcodes.code, intcodes.text, count(*)
    from   intcodes ic
    left outer join proc_event pe
           on proc_event.status=intcodes.code
    where  trunc(pe.que_status_stamp) = date '2008-05-14'

  3. #3
    Join Date
    May 2008
    Location
    Atlanta, GA
    Posts
    14
    I have tried your suggestion and this was the yielded result

    SQL> select incodes.code, intcodes.text, count(*)
    2 from intcodes ic
    3 left outer join proc_event pe
    4 on proc_event.status=intcodes.code
    5 where trunc(pe.que_status_stamp) = date '2008-05-14';
    on proc_event.status=intcodes.code
    *
    ERROR at line 4:
    ORA-00904: "INTCODES"."CODE": invalid identifier

    Can you please offer further assistance?

    Thanks,
    Anthony

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Correction:

    Code:
    select intcodes.code, intcodes.text, count(*)
    from   intcodes
    left outer join proc_event
           on proc_event.status=intcodes.code
    where  trunc(proc_event.que_status_stamp) = date '2008-05-14'

  5. #5
    Join Date
    May 2008
    Location
    Atlanta, GA
    Posts
    14
    Hmm: here is the output
    SQL> select intcodes.code, intcodes.text, count(*)
    2 from intcodes
    3 left outer join proc_event
    4 on proc_event.status=intcodes.code
    5 where trunc(proc_event.que_status_stamp) = date '2008-05-14';
    select intcodes.code, intcodes.text, count(*)
    *
    ERROR at line 1:
    ORA-00937: not a single-group group function

    Any ideas?

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    > Any ideas?
    The "GROUP BY" clause containing both columns from INTCODES table is missing in the end of the query.

    @PianoManBflat:
    By the way, what is the datatype of PROC_EVENT.QUE_STATUS_STAMP (as '14-MAY-08' is VARCHAR)? Also using 2 digits for year number is not good. I thought people found 4 digits less confusing at least from the year 2000.
    Are you also aware that using LIKE without wildcards acts as a simple comparision?

    @andrewst:
    I would try to avoid using TRUNC function on the date column in the WHERE clause as the query could not use (possible) index on that column. Although building function based index on TRUNC(column) is possible too, I would not rather do it.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by flyboy
    >@andrewst:
    I would try to avoid using TRUNC function on the date column in the WHERE clause as the query could not use (possible) index on that column. Although building function based index on TRUNC(column) is possible too, I would not rather do it.
    So would I really. I was just too lazy to write:
    Code:
    where  proc_event.que_status_stamp >= date '2008-05-14'
    and    proc_event.que_status_stamp < date '2008-05-15'

  8. #8
    Join Date
    May 2008
    Location
    Atlanta, GA
    Posts
    14
    Thanks tremendously for the input. I have executed the following statement and received the output below:

    SQL> select proc_event.status as "Status Code", intcodes.text as Status , count(*) as "Job Executions" from proc_event INNER JOIN intcodes ON proc_event.status=intcodes.code where que_status_stamp like '17-MAY-08' GROUP BY proc_event.status, intcodes.text;

    Status Code STATUS Job Executions
    ----------- ------------------------------ --------------
    0 1704
    1 RUNNING 751
    3 STARTING 728
    4 SUCCESS 723
    5 FAILURE 31
    6 TERMINATED 2
    10 RESTART 20

    7 rows selected.

    To flyboy, the datatype for PROC_EVENT.QUE_STATUS_STAMP is "QUE_STATUS_STAMP NOT NULL DATE"

    How can I include "sysdate -1" (or some other method to calculate yesderday's date) in the above statement to calculate yesterday date for the report? Is there a way to calculate the total from the "Job execution" column?

    Thanks,
    Anthony

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Code:
    select intcodes.code, intcodes.text, count(*)
    from   intcodes
    left outer join proc_event
           on proc_event.status=intcodes.code
    where  trunc(proc_event.que_status_stamp) = trunc(sysdate)-1
    or (to ensure index on que_status_stamp can be used):
    Code:
    select intcodes.code, intcodes.text, count(*)
    from   intcodes
    left outer join proc_event
           on proc_event.status=intcodes.code
    where  proc_event.que_status_stamp >= trunc(sysdate)-1
    and    proc_event.que_status_stamp < trunc(sysdate)
    Using "que_status_stamp like '17-MAY-08'" is bad practice as it forces an implicit conversion of que_status_stamp to a VARCHAR2 using the default date format mask. This may happen to work for you today, but is not guaranteed.

  10. #10
    Join Date
    May 2008
    Location
    Atlanta, GA
    Posts
    14
    This is the statement that has yeilded the result I was looking for:

    SQL> select proc_event.status as "Status Code", intcodes.text as "Status" , count(*) as "Job Executions" from proc_event INNER JOIN intcodes ON proc_event.status=intcodes.code where que_status_stamp >= trunc(sysdate)-1 and proc_event.que_status_stamp < trunc(sysdate) GROUP BY proc_event.status, intcodes.text;

    Status Code Status Job Executions
    ----------- ------------------------------ --------------
    0 2565
    1 RUNNING 890
    3 STARTING 929
    4 SUCCESS 847
    5 FAILURE 70
    6 TERMINATED 8
    8 INACTIVE 2
    10 RESTART 100

    8 rows selected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •