help with query 1

[Scunningham99]

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

 

[Scunningham99]

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

 

[LKBrwnDBA]

Try:

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

 

[Scunningham99]

perfect thanks

Sy UK

 

[ChrisHunt]

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