how to obtain all datas from 2 tables outer join 1

[fosa]

HI all

I have 2 tables
one which contents all the jobs to be done every night
and a second which contains the jobs which have really been executed.

CREATE TABLE UTIL_STATUS_BATCH
(
PROCEDURE_NAME VARCHAR2(1024 BYTE) NULL,
INFO_DATE DATE NULL,
TABLE_NAME VARCHAR2(100 BYTE) NULL,
ROWS_INSERTED NUMBER(15) NULL,
STATUS VARCHAR2(20 BYTE) NULL
)
;

CREATE TABLE UTIL_BATCH_DESC
(
PROCEDURE_NAME VARCHAR2(1024 BYTE) NULL,
TABLE_NAME VARCHAR2(80 BYTE) NULL
)

/


INSERT INTO UTIL_BATCH_DESC ( PROCEDURE_NAME, TABLE_NAME ) VALUES (
'INSERT_SWAP_SPREAD', 'SWAP_SPREAD');
INSERT INTO UTIL_BATCH_DESC ( PROCEDURE_NAME, TABLE_NAME ) VALUES (
'INSERT_CUR', 'CURRENCY_HISTO');
INSERT INTO UTIL_BATCH_DESC ( PROCEDURE_NAME, TABLE_NAME ) VALUES (
'INSERT_LIBOR', 'LIBOR_FIXINGS');
INSERT INTO UTIL_BATCH_DESC ( PROCEDURE_NAME, TABLE_NAME ) VALUES (
'INSERT_SWAP', 'SWAP_FIXINGS');
INSERT INTO UTIL_BATCH_DESC ( PROCEDURE_NAME, TABLE_NAME ) VALUES (
'INSERT_ALL_BETA', 'DAILY_BETA');
INSERT INTO UTIL_BATCH_DESC ( PROCEDURE_NAME, TABLE_NAME ) VALUES (
'INSERT_ALL_BOND_RATINGS', 'BOND_RATINGS_EVENTS');
INSERT INTO UTIL_BATCH_DESC ( PROCEDURE_NAME, TABLE_NAME ) VALUES (
'INSERT_ALL_BONDS_FIXINGS', 'BONDS_FIXINGS');
INSERT INTO UTIL_BATCH_DESC ( PROCEDURE_NAME, TABLE_NAME ) VALUES (
'INSERT_ALL_CDS_FIXINGS', 'CDS_FIXINGS');
INSERT INTO UTIL_BATCH_DESC ( PROCEDURE_NAME, TABLE_NAME ) VALUES (
'INSERT_ALL_CDS_INDEXFIXINGS', 'CDS_INDEX_FIXINGS');
INSERT INTO UTIL_BATCH_DESC ( PROCEDURE_NAME, TABLE_NAME ) VALUES (
'INSERT_ALL_FUTURES', 'FUTURES_FIXINGS');
INSERT INTO UTIL_BATCH_DESC ( PROCEDURE_NAME, TABLE_NAME ) VALUES (
'INSERT_ALL_SEC_FUND_EVENTS', 'SECURITY_FUNDAMENTAL_EVENTS');
INSERT INTO UTIL_BATCH_DESC ( PROCEDURE_NAME, TABLE_NAME ) VALUES (
'INSERT_ALL_SEC_VOLATILITY', 'SECURITY_VOLATILITY');
commit;

INSERT INTO UTIL_STATUS_BATCH ( PROCEDURE_NAME, INFO_DATE, TABLE_NAME, ROWS_INSERTED,
STATUS ) VALUES (
'OWNER_HISTO.INSERT_ALL_BETA', TO_Date( '10/14/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'DAILY_BETA', 2799, 'OK');
INSERT INTO UTIL_STATUS_BATCH ( PROCEDURE_NAME, INFO_DATE, TABLE_NAME, ROWS_INSERTED,
STATUS ) VALUES (
'OWNER_HISTO.INSERT_CUR', TO_Date( '10/14/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'CURRENCY_HISTO', 47, 'OK');
INSERT INTO UTIL_STATUS_BATCH ( PROCEDURE_NAME, INFO_DATE, TABLE_NAME, ROWS_INSERTED,
STATUS ) VALUES (
'OWNER_HISTO.INSERT_LIBOR', TO_Date( '10/14/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'LIBOR_FIXINGS', 117, 'OK');
INSERT INTO UTIL_STATUS_BATCH ( PROCEDURE_NAME, INFO_DATE, TABLE_NAME, ROWS_INSERTED,
STATUS ) VALUES (
'OWNER_HISTO.INSERT_SWAP', TO_Date( '10/14/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'SWAP_FIXINGS', NULL, 'KO');
INSERT INTO UTIL_STATUS_BATCH ( PROCEDURE_NAME, INFO_DATE, TABLE_NAME, ROWS_INSERTED,
STATUS ) VALUES (
'OWNER_HISTO.INSERT_SWAP_SPREAD', TO_Date( '10/14/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'SWAP_SPREAD', 113, 'OK');
INSERT INTO UTIL_STATUS_BATCH ( PROCEDURE_NAME, INFO_DATE, TABLE_NAME, ROWS_INSERTED,
STATUS ) VALUES (
'OWNER_HISTO.INSERT_SWAP_SPREAD', TO_Date( '10/14/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'SWAP_SPREAD', 92, 'OK');
INSERT INTO UTIL_STATUS_BATCH ( PROCEDURE_NAME, INFO_DATE, TABLE_NAME, ROWS_INSERTED,
STATUS ) VALUES (
'OWNER_HISTO.INSERT_SWAP', TO_Date( '10/14/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'SWAP_FIXINGS', 113, 'OK');
INSERT INTO UTIL_STATUS_BATCH ( PROCEDURE_NAME, INFO_DATE, TABLE_NAME, ROWS_INSERTED,
STATUS ) VALUES (
'OWNER_HISTO.INSERT_ALL_BONDS_FIXINGS', TO_Date( '10/14/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'BOND_RATING_EVENTS', 6738, 'OK');
INSERT INTO UTIL_STATUS_BATCH ( PROCEDURE_NAME, INFO_DATE, TABLE_NAME, ROWS_INSERTED,
STATUS ) VALUES (
'OWNER_HISTO.INSERT_ALL_CDS_INDEXFIXINGS', TO_Date( '10/14/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'CDS_INDEX_FIXINGS', 15, 'OK');
INSERT INTO UTIL_STATUS_BATCH ( PROCEDURE_NAME, INFO_DATE, TABLE_NAME, ROWS_INSERTED,
STATUS ) VALUES (
'OWNER_HISTO.INSERT_ALL_CDS_INDEXFIXINGS', TO_Date( '10/14/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'CDS_INDEX_FIXINGS', 0, 'OK');
INSERT INTO UTIL_STATUS_BATCH ( PROCEDURE_NAME, INFO_DATE, TABLE_NAME, ROWS_INSERTED,
STATUS ) VALUES (
'OWNER_HISTO.INSERT_ALL_CDS_INDEXFIXINGS', TO_Date( '10/14/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'CDS_INDEX_FIXINGS', 0, 'KO');
INSERT INTO UTIL_STATUS_BATCH ( PROCEDURE_NAME, INFO_DATE, TABLE_NAME, ROWS_INSERTED,
STATUS ) VALUES (
'INSERT_ALL_FUTURES', TO_Date( '10/15/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'FUTURES_FIXINGS', 710, 'OK');
INSERT INTO UTIL_STATUS_BATCH ( PROCEDURE_NAME, INFO_DATE, TABLE_NAME, ROWS_INSERTED,
STATUS ) VALUES (
'INSERT_ALL_CDS_INDEXFIXINGS', TO_Date( '10/15/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'CDS_INDEX_FIXINGS', 25, 'OK');
INSERT INTO UTIL_STATUS_BATCH ( PROCEDURE_NAME, INFO_DATE, TABLE_NAME, ROWS_INSERTED,
STATUS ) VALUES (
'INSERT_ALL_CDS_FIXINGS', TO_Date( '10/14/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'CDS_FIXINGS', 963, 'WARNING');






So I begun with this script

select d.procedure_name,d.table_name,b.rows_inserted,decode (b.status,null,'MUST BE CHECKED',b.status) status
from UTIL_BATCH_DESC D right outer join UTIL_STATUS_BATCH B
on ( d.procedure_name =b.PROCEDURE_NAME)
where b.info_date=trunc(sysdate) -1;


but I don't have the result I attended
I want all datas in UTIL_BATCH_DESC and not only
the datas of the join


Best regards

 

[Dagon]

select d.procedure_name,d.table_name,b.rows_inserted,decode (b.status,null,'MUST BE CHECKED',b.status) status
from UTIL_BATCH_DESC D left outer  join (select * from UTIL_STATUS_BATCH  where info_date=trunc(sysdate) -1) b
on ( d.procedure_name =b.PROCEDURE_NAME)

 

[fosa]

I found an old syntax
that works
but I want now to have the ANSI one ;-)

select d.procedure_name,d.table_name,b.rows_inserted,decode (b.status,null,'MUST BE CHECKED',b.status) status
from UTIL_BATCH_DESC D , UTIL_STATUS_BATCH B
where d.procedure_name =b.PROCEDURE_NAME(+)
and b.info_date(+)=trunc(sysdate) -1;


Best regards

 

[fosa]

OK ,

i've not seen you post
thanks I works fine