Please help me write a query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Please help me write a query

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Please help me write a query

    Hi Guys,

    Pl. help me write a query.

    I have a foll. qry.

    select fcp.concurrent_program_name||' - '||
    fcpt.user_concurrent_program_name "Program"
    ,count(*) "SLA miss count"
    from
    sla_request_data gsrd,
    fnd_concurrent_requests fcr,
    fnd_concurrent_programs fcp,
    fnd_concurrent_programs_tl fcpt
    where
    gsrd.request_id = fcr.request_id
    and fcr.concurrent_program_id = fcp.concurrent_program_id
    and fcpt.concurrent_program_id = fcp.concurrent_program_id
    and fcpt.language = 'US'
    and fcr.program_application_id = fcp.application_id
    and fcpt.application_id = fcp.application_id
    and gsrd.start_date between '01-SEP-2005' and '30-SEP-2005'
    group by fcp.concurrent_program_name
    ,fcpt.user_concurrent_program_name

    This gives output...

    Program SLA miss count
    FAPPT - Process Pending Transactions 1
    POPDI - GEMS PDI PO Report Optio 1
    RVCTP - Receiving Transaction Processor 9
    APPBCF - Confirm Payment Batch 10

    I want to have foll. output (for example)

    Program SLA miss total %miss
    FAPPT - Process Pending Transactions 1 100 1
    POPDI - GEMS PDI PO Report Optio 1 90 .9
    RVCTP - Receiving Transaction Processor 9 1000 .01
    APPBCF - Confirm Payment Batch 10 1000 .01

    total colum is a count of total requests in fnd_concurrent_requests table for particular program in the month of Sep-05. I want this column to show %miss for a parti. program.

    How can I do this?

    Pl. help.

    thanks in adv.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    It's difficult to answer this without understanding your table structures. You seem to want to compare "misses" with total records. Your first query must be returning the "misses". How would you return total records on your database ? Once you've got that, you can join the two queries together and get the information you want.

  3. #3
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi Scorby,

    Thanks for your response.

    Here is the structure.

    SLA_REQUEST_DATA
    --------------------
    start_date Date
    request_id number

    FND_CONCURRENT_REQUESTS
    -----------------------------
    request_id Number
    concurrent_program_id Number

    FND_CONCURRENT_PROGRAMS
    ------------------------------
    concurrent_program_id Number
    Concurrent_program_name VC

    I am joining SLA_REQUEST_DATA with FND_CONCURRENT_REQUESTS table to bring the concurrent_program_id and then joining with FND_CONCURRENT_PROGRAMS to bring program name.

    Total count/records for a particular concurrent_program_id is in fnd_concurrent_requests table. My problem is how to get this count in the same query where I get misses.

    Thanks in Adv.
    Last edited by samdba; 10-19-2005 at 04:13 AM.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  4. #4
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    This doesn't make any sense. Your original query retrieves "misses" by selecting from FIND_CONCURRENT_REQUESTS. But you say also want to get total records from the same table. If they are both coming from the same table, then ther must be something like a type which distinguishes between a missed record and a successful record. Or is it the fact that there is a match in the sla_request_data which determines that something "missed" ?

  5. #5
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi Scorby,

    Thanks again!

    There is no such column in FND_CONCURRENT_REQUESTS table which tells that the request was a success. It has all the rows for a particular concurrent_program_id out which some rows are getting populated in SLA_REQUEST_DATA which had SLA miss. SLA_REQUEST_DATA has only those request_ids where had SLA miss.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  6. #6
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    I think what you need is something like this:


    Code:
    select fcp.concurrent_program_name||' - '||
    fcpt.user_concurrent_program_name "Program"
    ,count(gsrd.request_id) "SLA miss count",
    count(*) total,
    ((count(gsrd.request_id)/count(*)) * 100 prop
    from 
    (select * from sla_request_data 
    where start_date between '01-SEP-2005' and '30-SEP-2005') gsrd,
    fnd_concurrent_requests fcr,
    fnd_concurrent_programs fcp,
    fnd_concurrent_programs_tl fcpt
    where 
    gsrd.request_id (+) = fcr.request_id
    and fcr.concurrent_program_id = fcp.concurrent_program_id
    and fcpt.concurrent_program_id = fcp.concurrent_program_id
    and fcpt.language = 'US'
    and fcr.program_application_id = fcp.application_id
    and fcpt.application_id = fcp.application_id
    group by fcp.concurrent_program_name
    ,fcpt.user_concurrent_program_name
    The drawback is that this would return rows in dates other than September as well. Is there a start_date on the find_concurrent_requests table as well ?

Posting Permissions

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


Click Here to Expand Forum to Full Width