Asked to run a query against a two column table, one column having a procedure name and the other with the date the procedure was last ran.
e.g.
PROC_NAME START_DTM
------------------------------ ---------
TVM Cash Balance 05-NOV-20
TVM Cash Balance 12-NOV-20
TVM Cash Balance 02-DEC-20
TVM Cash Balance 01-DEC-20
TVM Cash Balance 03-DEC-20
TVM Cash Balance 04-DEC-20
TVM Cash Balance 30-SEP-20
TVM Cash Balance 18-SEP-20
TVM Cash Balance 01-SEP-20
TVM Cash Balance 29-SEP-20
TVM Cash Balance 12-SEP-20
------------------------------ ---------
TVM Cash Balance 05-NOV-20
TVM Cash Balance 12-NOV-20
TVM Cash Balance 02-DEC-20
TVM Cash Balance 01-DEC-20
TVM Cash Balance 03-DEC-20
TVM Cash Balance 04-DEC-20
TVM Cash Balance 30-SEP-20
TVM Cash Balance 18-SEP-20
TVM Cash Balance 01-SEP-20
TVM Cash Balance 29-SEP-20
TVM Cash Balance 12-SEP-20
We needed to select the last date for each procedure.
This works :
select proc_name, max(start_dtm) from dw_main.proc_run_log where proc_name in (select distinct(proc_name) from dw_main.proc_run_log) group by proc_name order by proc_name;
PROC_NAME MAX(START
------------------------------ ---------
Summarize Tap RW Error Summary 04-DEC-20
Summarize Tap Speed Summary 04-DEC-20
Summarize Txn Timing 04-DEC-20
TVM Cash Balance 04-DEC-20
Update Card Statuses 04-DEC-20
Summarize Txn Timing 04-DEC-20
TVM Cash Balance 04-DEC-20
Update Card Statuses 04-DEC-20
....
131 rows selected.
We wanted to double check (and to show how this could also be achieved in pl/sql) to be absolutely certain so put this together (the sql is also easier to understand ):
create or replace procedure ruslist
is
proc_rus varchar2(30);
start_rus date;
CURSOR d1 IS SELECT distinct proc_name from dw_main.proc_run_log order by proc_name;
CURSOR d2 IS SELECT max(start_dtm) from dw_main.proc_run_log where proc_name = proc_rus;
BEGIN
open d1;
LOOP
FETCH d1 into proc_rus;
EXIT when d1% NOTFOUND;
dbms_output.put( proc_rus);
open d2;
LOOP
FETCH d2 into start_rus;
EXIT when d2% NOTFOUND;
dbms_output.put_line(' '||start_rus);
END LOOP;
CLOSE d2;
END LOOP;
CLOSE d1;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END ruslist;
is
proc_rus varchar2(30);
start_rus date;
CURSOR d1 IS SELECT distinct proc_name from dw_main.proc_run_log order by proc_name;
CURSOR d2 IS SELECT max(start_dtm) from dw_main.proc_run_log where proc_name = proc_rus;
BEGIN
open d1;
LOOP
FETCH d1 into proc_rus;
EXIT when d1% NOTFOUND;
dbms_output.put( proc_rus);
open d2;
LOOP
FETCH d2 into start_rus;
EXIT when d2% NOTFOUND;
dbms_output.put_line(' '||start_rus);
END LOOP;
CLOSE d2;
END LOOP;
CLOSE d1;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END ruslist;
......
Summarize Tap RW Error Summary 04-DEC-20
Summarize Tap Speed Summary 04-DEC-20
Summarize Txn Timing 04-DEC-20
TVM Cash Balance 04-DEC-20
Update Card Statuses 04-DEC-20
Summarize Tap Speed Summary 04-DEC-20
Summarize Txn Timing 04-DEC-20
TVM Cash Balance 04-DEC-20
Update Card Statuses 04-DEC-20
PL/SQL procedure successfully completed.
SQL>
Happy days.
