Re: Outer joins; need some assistance

eastephe_at_rodan.syr.edu (Eric A. Stephens) writes:

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

It seems to me that you could do this in 2 SQL statements, adding the intermediate results together in PB script. Then you wouldn't have to worry about missing rows in one table or the other. For example:

double cr_sum, cr_sum1, cr_sum2
double db_sum, db_sum1, db_sum2

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

       sum(TOPS."VACCT_TRANS"."DB_AMT")
INTO :cr_sum1, :db_sum1
FROM "TOPS"."VACCT_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 )

SELECT sum(TOPS."VREMIT_TRANS"."LDGR_CR_AMT") + 
       sum(TOPS."VREMIT_TRANS"."PYMNT_AMT"),   
       sum(TOPS."VREMIT_TRANS"."LDGR_DB_AMT")  

WHERE ( "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 )

cr_sum = cr_sum1 + cr_sum2
db_sum = db_sum1 + db_sum2

Good luck.

--MDC Received on Sun Oct 09 1994 - 19:38:42 CET