Re: Outer joins; need some assistance

From: Gary71 <gary71_at_aol.com>
Date: 8 Oct 1994 23:16:02 -0400
Message-ID: <377n9i$2rd_at_newsbf01.news.aol.com>

In article <374vgu$spj_at_newstand.syr.edu>, eastephe_at_rodan.syr.edu (Eric A. Stephens) writes:

>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 )

  What it looks like you wanted to do is perform an outer-join both tables.
This is not possible.Oracle only allows outer-joins on columns from one table in a query.

  There are a couple ways around it. From your example, I would recommend using
UNION to merge two select statements, one from each table. First, create a view.
You can sum the values from each table (AMOUNT field below) and include all
columns used in your where clause.

Create View ALL_TRANS (

              AMOUNT,
              BATCH_NO,
              STUB_DTE,
              BATCH_SRC_CD
            )
SELECT  sum(LDGR_CR_AMT) + sum(PYMNT_AMT) +  sum(LDGR_DB_AMT)  AMOUNT
              BATCH_NO,
              STUB_DTE,
              BATCH_SRC_CD

FROM "TOPS"."VREMIT_TRANS"
UNION
select sum(CR_AMT) + sum(DB_AMT) AMOUNT

           BATCH_NO,
           STUB_DTE,
           BATCH_SRC_CD

 FROM "TOPS"."VACCT_TRANS" ; You can get your grand total by using this script...

select sum(amount)
from all_trans
where batch_no = :batch
and stub+_date = :stub_dte
and batch_src_ce = :batch_src_ce;   Received on Sun Oct 09 1994 - 04:16:02 CET