Oracle SQL - Computed Column via Subquery

[SpeedThink]

Currently, I have Oracle version 8.1.7 and have the following SQL that has not worked:

Select pe.acct_no, pe.expected_reimbursement, pe.total_charges, ep.total_payments, pe.contract_no, pe.admit_date, pe.allowable, balance (computed), calculated_allowable(computed)
from patient_encounter pe, encounter_transactions et,
encounter_payor ep
where pe.contract_no = 'XA3'

"Pe.allowable" should be the sum of all payment amounts only for transaction codes in ('48005', '48006','48060'). Some accounts will have data for these transaction codes. Some will not.

The "Balance" is computed as expected_reimbursement less the "total payments."

What is the most expedient method to obtain the "allowable amount" and the "calculated allowable" amount (Calculated allowable is computed as "Total Charges" less the "Allowable" amount) in the same SQL query without using a limit local join within Brio Intelligence or running 2 to 3 additional SQL queries? SubQuery? Union?

Thanks in advance.

 

[LKBrwnDBA]

You will need something like this:

Select Pe.Acct_No
     , Pe.Expected_Reimbursement
     , Pe.Total_Charges
     , Ep.Total_Payments
     , Pe.Contract_No
     , Pe.Admit_Date
     , Sum(Case
             When Et.Trans_Cd In ('48005', '48006','48060')
             Then Ep.Total_Payments 
             Else 0 End) "Pe.Allowable"
     , Sum(Pe.Expected_Reimbursement 
         - Ep.Total_Payments) "Balance (Computed)"
     , Sum(Pe.Total_Charges 
         - Case
             When Et.Trans_Cd In ('48005', '48006','48060')
             Then Ep.Total_Payments
             Else 0 End)) "Calculated_Allowable(Computed)"
  From Patient_Encounter Pe
     , Encounter_Transactions Et
     , Encounter_Payor Ep 
 Where Pe.Contract_No = 'XA3'
   And Et.Contract_No = Pe.Contract_No
   And Ep.Payor = Pe.Payor
 Group By Pe.Acct_No
        , Pe.Expected_Reimbursement
        , Pe.Total_Charges
        , Ep.Total_Payments
        , Pe.Contract_No
        , Pe.Admit_Date;

Also note that you will need to add the conditions for joining all three tables.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb