Outer joins; need some assistance

From: Eric A. Stephens <eastephe_at_rodan.syr.edu>
Date: 8 Oct 1994 02:18:06 GMT
Message-ID: <374vgu$spj_at_newstand.syr.edu>


We have a set of two Oracle tables which together make up a group of transactions for a system. We are attempted to perform the following SQL
but realize that only one table (either one) may have data in it fitting the where
clause while the other (again, either one) may not have any data. I've seen
the Oracle manual where it discusses outer joins, but the example employs
a one-to-many type scheme. This scenario doesn't follow the example in the
manual. Any clues how to code the SQL for this type of situation?

  SELECT sum(TOPS."VACCT_TRANS"."CR_AMT") +

	 sum(TOPS."VREMIT_TRANS"."LDGR_CR_AMT") + 
	sum(TOPS."VREMIT_TRANS"."PYMNT_AMT"),   
         sum(TOPS."VACCT_TRANS"."DB_AMT") +
sum(TOPS."VREMIT_TRANS"."LDGR_DB_AMT")  
    FROM "TOPS"."VACCT_TRANS",   
         "TOPS"."VREMIT_TRANS"  
   WHERE ( "TOPS"."VACCT_TRANS"."BATCH_NO" = :as_batch_no ) AND  

( "TOPS"."VACCT_TRANS"."STUB_DTE" = :adt_stb_dte ) AND
( "TOPS"."VACCT_TRANS"."BATCH_SRC_CD" = :as_batch_src_cd )
AND

( "TOPS"."VREMIT_TRANS"."BATCH_NO" = :as_batch_no ) AND
( "TOPS"."VREMIT_TRANS"."STUB_DTE" = :adt_stb_dte ) AND
( "TOPS"."VREMIT_TRANS"."BATCH_SRC_CD" = :as_batch_src_cd )
Received on Sat Oct 08 1994 - 03:18:06 CET

Original text of this message