INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

help with query

help with query

(OP)
I have the following query which reports on concurrent program output. The problem is I don't want to report and count on rows with the inner join (+), so the count should return 0 and not 1. The date is also NULL


SELECT cpv.user_concurrent_program_name "Concurrent Program Name",
cpv.concurrent_program_name "Program Short Name",
efv.application_name "Application",
cpv.enabled_flag "Enabled Flag",
cpv.output_file_type "Output Format",
fu.user_name "Created By (userid)",
DECODE(efv.execution_method_code,
'I', 'PL/SQL Stored Procedure',
'H', 'Host',
'S', 'Immediate',
'J', 'Java Stored Procedure',
'K', 'Java Concurrent Program',
'M', 'Multi Language Function',
'P', 'Oracle Reports',
'B', 'Request Set Stage Function',
'A', 'Spawned',
'L', 'SQL*Loader',
'Q', 'SQL*Plus',
'E', 'Pearl Concurrent Programm',
'Unknown') "Execution Method",
efv.executable_name "Executable Name",
efv.execution_file_name "Execution Filename",
MAX(fcc.actual_start_date) DATE_last_run , --added 02 Jan 14
count(*) Number_of_times_run -- added 02 Jan 14, joined to fcc for number of times run
FROM fnd_executables_form_v efv,
fnd_concurrent_programs_vl cpv,
fnd_user fu,
fnd_concurrent_requests fcc
WHERE efv.executable_id = cpv.executable_id
AND fcc.concurrent_program_id (+) = cpv.concurrent_program_id
AND efv.application_id = cpv.application_id
AND cpv.created_by = fu.user_id
AND cpv.user_concurrent_program_name like '%LXX%'-- <change it>
GROUP BY cpv.user_concurrent_program_name,cpv.concurrent_program_name,efv.application_name,cpv.enabled_flag,cpv.output_file_type,fu.user_name,
DECODE(efv.execution_method_code,'I', 'PL/SQL Stored Procedure',
'H', 'Host',
'S', 'Immediate',
'J', 'Java Stored Procedure',
'K', 'Java Concurrent Program',
'M', 'Multi Language Function',
'P', 'Oracle Reports',
'B', 'Request Set Stage Function',
'A', 'Spawned',
'L', 'SQL*Loader',
'Q', 'SQL*Plus',
'E', 'Pearl Concurrent Programm',
'Unknown'),
efv.executable_name,efv.execution_file_name


Sy UK

RE: help with query

(OP)
I have modified the query now to return "never Run". So I just need the count to return 0 when the date_last_run returns "Never Run"

Any Ideas?


SELECT cpv.user_concurrent_program_name "Concurrent Program Name",
cpv.concurrent_program_name "Program Short Name",
efv.application_name "Application",
cpv.enabled_flag "Enabled Flag",
cpv.output_file_type "Output Format",
fu.user_name "Created By (userid)",
DECODE(efv.execution_method_code,
'I', 'PL/SQL Stored Procedure',
'H', 'Host',
'S', 'Immediate',
'J', 'Java Stored Procedure',
'K', 'Java Concurrent Program',
'M', 'Multi Language Function',
'P', 'Oracle Reports',
'B', 'Request Set Stage Function',
'A', 'Spawned',
'L', 'SQL*Loader',
'Q', 'SQL*Plus',
'E', 'Pearl Concurrent Programm',
'Unknown') "Execution Method",
efv.executable_name "Executable Name",
efv.execution_file_name "Execution Filename",
NVL(to_char(MAX(fcc.actual_start_date)),'Never Run') DATE_last_run , --SC added 02 Jan 14
count(*) Number_of_times_run --SC added 02 Jan 14, joined to fcc for number of times run
FROM fnd_executables_form_v efv,
fnd_concurrent_programs_vl cpv,
fnd_user fu,
fnd_concurrent_requests fcc
WHERE efv.executable_id = cpv.executable_id
AND fcc.concurrent_program_id (+) = cpv.concurrent_program_id
AND efv.application_id = cpv.application_id
AND cpv.created_by = fu.user_id
AND cpv.user_concurrent_program_name like '%LBH%'-- <change it>
GROUP BY cpv.user_concurrent_program_name,cpv.concurrent_program_name,efv.application_name,cpv.enabled_flag,cpv.output_file_type,fu.user_name,
DECODE(efv.execution_method_code,'I', 'PL/SQL Stored Procedure',
'H', 'Host',
'S', 'Immediate',
'J', 'Java Stored Procedure',
'K', 'Java Concurrent Program',
'M', 'Multi Language Function',
'P', 'Oracle Reports',
'B', 'Request Set Stage Function',
'A', 'Spawned',
'L', 'SQL*Loader',
'Q', 'SQL*Plus',
'E', 'Pearl Concurrent Programm',
'Unknown'),
efv.executable_name,efv.execution_file_name



XXX Upload Progra AC_AR_CUST_INTRF_UPLOAD Custom Code Y TEXT ANONYMOUS SQL*Plus AC_AR_CUST_INTF AC_AR_CUST_INTF Never Run 1


The reason its returning 1 is because of the (+) join, so I need the count to return a 0 when the last_run_date returns a NULL value ?

Any help is greatly appreciated

Sy UK

RE: help with query

Try:

CODE

SELECT . . .
      , SUM(DECODE(  Fcc.Actual_Start_Date,NULL,0,1))
        Number_Of_Times_Run --SC added 02 Jan 14, joined to fcc for number of times run
   FROM . . . 
;)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

RE: help with query

(OP)
perfect thanks

Sy UK

RE: help with query

The COUNT() function returns the count of the number of rows for which the parameter passed to it is not null. COUNT(*) is a special case, and returns the count of all rows.

So to get what you want, you just have to put a non-null column from the outer joined table - because it will be not null where a row exists, and null where it doesn't. In your case, putting COUNT(fcc.concurrent_program_id) instead of COUNT(*) should do the trick.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close