Correlated Subquery within a Passthru Query 2

[LevelThought]

Context
I need to append ,on a daily basis, to the local Microsoft Access table all accounts that had a payment interface the Oracle table titled “ACCOUNT_PAYMENT_DETAIL” 1 day before the present day and update, on a daily basis, several fields on a subset of the records in the local Access table that had one or more of the following events happen the day before; a payment, and/or a change in account id, and/or a change in expected reimbursement? The account id field is located in the Oracle table titled “ACCOUNT_PAYOR” and the expected reimbursement field is on the Oracle table titled “CUSTOMER_ENCOUNTER”.
Over the past 9 months, I have explored Access SQL, ADO, Crosstab Queries and now currently exploring the use of correlated subqueries within a passthru query from Microsoft Access 2003 to an Oracle 8.1.7 database.

Problem
The SQL script below "pulls" the wrong value for the "LatestPayorPymtDate."


Question
How should the SQL script be modified so that I will not only pull the correct value for “LatestPayorPymtDate” but also obtain values for “AmtFirstPayorPymt”, “DateFirstPayorPymt”, and “AmtLastPayorPymt”?


SELECT distinct AL1.ACCOUNT_ID as AcctOrig, AL1.ACCOUNT_ID as AcctCurrent, AL7.ENCOUNTER_NO as EncNo, AL2.CUSTOMER_TYPE as CustType, AL6.LAST_NAME as LastName, AL6.FIRST_NAME as FirstName, AL6.RECORDS_NO as RN, AL2.ADMIT_DATE as AdmitDate, AL2.DISCHARGE_DATE as DischDate, AL2.DATE_BILLED as DateBilled, AL2.TOTAL_PAYMENTS as TotPymts, AL2.TOTAL_CHARGES as TotChgOrig, AL2.TOTAL_CHARGES as TotChgCurrent, AL2.EXPECTED_PAYMENT as ExpReimbOrig, AL2.EXPECTED_PAYMENT as ExpReimbCurrent, AL1.TOTAL_PAYMENTS as TotInsPymtsOrig, AL1.TOTAL_PAYMENTS as TotInsPymtsCurrent, AL2.EXPECTED_PAYMENT - AL1.TOTAL_PAYMENTS as BalAfterInsPymts, AL2.EXPECTED_PAYMENT - AL2.TOTAL_PAYMENTS as BalAfterAllPymts, AL1.TOTAL_PAYMENTS / AL2.EXPECTED_PAYMENT as OrigRatio , AL1.TOTAL_PAYMENTS / AL2.EXPECTED_PAYMENT as RatioLatest, AL2.TOTAL_PAYMENTS - AL1.TOTAL_PAYMENTS as OthPymts, AL2.TOTAL_CHARGES - (AL2.NONCOVERED_DC_CHARGES + AL2.NONCOVERED_CT_CHARGES) as CoveredCharges, AL2.TOTAL_CHARGES - Sum (AL5.ADJUSTMENT_AMOUNT) as AllowOrig, AL2.TOTAL_CHARGES - Sum ( AL5.ADJUSTMENT_AMOUNT) as AllowCurrent, AL7.DATE_INTERFACED as DateIdentified, AL2.LENGTH_OF_STAY as LOS, AL2.DRG_NO as DrgNo, MAX ( AL7.PAYMENT_DATE ) as LatestPayorPymtDate, AL2.EXPECTED_PAYMENT - (AL2.TOTAL_CHARGES - Sum (AL5.ADJUSTMENT_AMOUNT)) as VarianceCurrent, AL2.EXPECTED_PAYMENT - (AL2.TOTAL_CHARGES - Sum (AL5.ADJUSTMENT_AMOUNT)) as VarianceOrig

FROM CV_OCXVW.ACCOUNT_PAYOR AL1, CV_OCXVW.CUSTOMER_ENCOUNTER AL2, CV_OCXVW.ACCOUNT_TRANSACTION_DETAILS AL5, CV_OCXVW.CUSTOMER AL6, CV_OCXVW.ACCOUNT_PAYMENT_DETAIL AL7 WHERE ( AL2.ENCOUNTER_NO = AL1.ENCOUNTER_NO AND AL2.ENCOUNTER_NO=AL5.ENCOUNTER_NO AND AL6.CUSTOMER_NO=AL2.CUSTOMER_NO AND AL7.ENCOUNTER_NO=AL1.ENCOUNTER_NO) AND (AL1.ACCOUNT_ID Not In ('D15','D16','D17'))
AND AL2.EXPECTED_PAYMENT>0 AND AL5.TRANSACTION_CODE in ('86004','86020','86035','86036','86037') AND AL1.RANK=1 AND AL7.TRANSACTION_CODE in ('47003','47008','47009','47010','47011','47012','47013') AND AL7.PAYMENT_DATE IN (SELECT AL14.PAYMENT_DATE FROM CV_OCXVW.ACCOUNT_PAYMENT_DETAIL AL14 WHERE (AL14.TRANSACTION_CODE in ('47003','47008','47009','47010','47011','47012', '47013'))) AND trunc(AL7.DATE_INTERFACED) = trunc (sysdate) - 1

GROUP BY AL1.ACCOUNT_ID, AL1.ACCOUNT_ID, AL7.ENCOUNTER_NO, AL2.CustType, AL6.LAST_NAME, AL6.FIRST_NAME, AL6.RECORDS_NO, AL2.ADMIT_DATE, AL2.DISCHARGE_DATE, AL2.DATE_BILLED, AL2.TOTAL_PAYMENTS, AL2.TOTAL_CHARGES, AL2.TOTAL_CHARGES, AL2.EXPECTED_PAYMENT, AL2.EXPECTED_PAYMENT, AL1.TOTAL_PAYMENTS, AL1.TOTAL_PAYMENTS, AL2.EXPECTED_PAYMENT - AL1.TOTAL_PAYMENTS, AL2.EXPECTED_PAYMENT - AL2.TOTAL_PAYMENTS, AL1.TOTAL_PAYMENTS / AL2.EXPECTED_PAYMENT, AL1.TOTAL_PAYMENTS / AL2.EXPECTED_PAYMENT, AL2.TOTAL_PAYMENTS - AL1.TOTAL_PAYMENTS, AL2.TOTAL_CHARGES - (AL2.NONCOVERED_DC_CHARGES + AL2.NONCOVERED_CT_CHARGES), AL7.DATE_INTERFACED,AL7.DATE_UPDATED, AL2.LENGTH_OF_STAY, AL2.DRG_NO HAVING AL2.EXPECTED_PAYMENT - AL1.TOTAL_PAYMENTS >=5000
ORDER BY 26


Thanks in advance.

 

[SantaMufasa]

Level,

The reason that you have received no responses back yet is the sheer volume of your code posting...it's scary. I'll guarantee your responses will rise if you can compose an alternate, minimalist code posting that creates a proof of concept for what you want, but is tens of times simpler...Unless you want us to do your coding for you, you don't need all of those expressions (for us to trip over) to get a proof of concept.

Looking forward to a simpler example,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LevelThought]

Sorry about that.

Displayed below is a portion of the SQL script that was originally displayed.

I am trying to use a Passthru query within Microsoft Access 2003 to append records from an Oracle 8.1.7 database to a local Microsoft Access table as well as update records on a daily basis.

Currently, the SQL script is incorrectly "pulling" the first payor payment, not the last payor payment.

How should the SQL script be modified so that I will obtain the following “LatestPayorPymtDate” ,“AmtFirstPayorPymt”,
“DateFirstPayorPymt”, and “AmtLastPayorPymt”?

(Note, a payor payment is recorded in the Account_Payment_Detail table under the following transaction_codes; '47003','47008','47009','47010','47011','47012','47013'.

The adjustment amounts used in the calculation of "AllowOrig" uses the following transaction codes from the Account_Transaction_Details table; '86004','86020','86035','86036','86037'. )


SQL Script

SELECT distinct AL1.ACCOUNT_ID as AcctOrig, AL7.ENCOUNTER_NO as EncNo,
AL2.TOTAL_CHARGES - Sum (AL5.ADJUSTMENT_AMOUNT) as AllowOrig, MAX ( AL7.PAYMENT_DATE ) as
LatestPayorPymtDate, AL2.EXPECTED_PAYMENT - (AL2.TOTAL_CHARGES - Sum
(AL5.ADJUSTMENT_AMOUNT)) as VarianceCurrent

FROM CV_OCXVW.ACCOUNT_PAYOR AL1, CV_OCXVW.CUSTOMER_ENCOUNTER AL2,
CV_OCXVW.ACCOUNT_TRANSACTION_DETAILS AL5, CV_OCXVW.CUSTOMER AL6, CV_OCXVW.ACCOUNT_PAYMENT_DETAIL AL7 WHERE ( AL2.ENCOUNTER_NO = AL1.ENCOUNTER_NO AND
AL2.ENCOUNTER_NO=AL5.ENCOUNTER_NO AND AL6.CUSTOMER_NO=AL2.CUSTOMER_NO AND
AL7.ENCOUNTER_NO=AL1.ENCOUNTER_NO) AND (AL1.ACCOUNT_ID Not In ('D15','D16','D17'))AND AL5.TRANSACTION_CODE in ('86004','86020','86035','86036','86037') AND AL1.RANK=1 AND AL7.TRANSACTION_CODE in ('47003','47008','47009','47010','47011','47012','47013') AND AL7.PAYMENT_DATE IN (SELECT AL14.PAYMENT_DATE FROM CV_OCXVW.ACCOUNT_PAYMENT_DETAIL AL14

WHERE (AL14.TRANSACTION_CODE in ('47003','47008','47009','47010','47011','47012', '47013'))) AND trunc(AL7.DATE_INTERFACED) = trunc (sysdate) - 1

GROUP BY AL1.ACCOUNT_ID, AL7.ENCOUNTER_NO,
HAVING AL2.EXPECTED_PAYMENT - AL1.TOTAL_PAYMENTS >=5000
ORDER BY 26

 

[SantaMufasa]

Level,

Since it appears that your need has to do with isolating data that relates to an "Earliest" date and a "Latest" date, I'll show you this method (that extracts data for the employee with the "Earliest" start_date and likewise for the "Latest" start_date from an employee table):

select first_name||' '||last_name Employee, start_date
from s_emp
where start_date = (select min(start_date) from s_emp);

EMPLOYEE         START_DAT
---------------- ---------
Audry Ropeburn   04-MAR-90

select first_name||' '||last_name Employee, start_date
from s_emp
where start_date = (select max(start_date) from s_emp);

EMPLOYEE         START_DAT
---------------- ---------
Carmen Velasquez 01-FEB-06

Let us know if you have questions while extrapolating this technique to your code.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LevelThought]

Mufasa,

I will try this. I noticed you provided two sql scripts - one for the earliest start date and one for the latest start date.

How would I combine this into just 1 sql script?

 

[SantaMufasa]

Anytime you wish to combine queries, a simple, effective method to consider is to try the "UNION" operator:

select first_name||' '||last_name Employee, start_date
from s_emp
where start_date = (select min(start_date) from s_emp)
[b]union[/b]
select first_name||' '||last_name Employee, start_date
from s_emp
where start_date = (select max(start_date) from s_emp);


EMPLOYEE         START_DAT
---------------- ---------
Audry Ropeburn   04-MAR-90
Carmen Velasquez 01-FEB-06

Let us know if you like this technique.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[ddiamond]

Just for reference, I've listed your original query reformatted so that it is easier to read.

SELECT DISTINCT 
  AL1.ACCOUNT_ID as AcctOrig,
  AL1.ACCOUNT_ID as AcctCurrent,
  AL7.ENCOUNTER_NO as EncNo,
  AL2.CUSTOMER_TYPE as CustType,
  AL6.LAST_NAME as LastName,
  AL6.FIRST_NAME as FirstName,
  AL6.RECORDS_NO as RN,
  AL2.ADMIT_DATE as AdmitDate,
  AL2.DISCHARGE_DATE as DischDate,
  AL2.DATE_BILLED as DateBilled,
  AL2.TOTAL_PAYMENTS as TotPymts,
  AL2.TOTAL_CHARGES as TotChgOrig,
  AL2.TOTAL_CHARGES as TotChgCurrent,
  AL2.EXPECTED_PAYMENT as ExpReimbOrig,
  AL2.EXPECTED_PAYMENT as ExpReimbCurrent,
  AL1.TOTAL_PAYMENTS as TotInsPymtsOrig,
  AL1.TOTAL_PAYMENTS as TotInsPymtsCurrent,
  AL2.EXPECTED_PAYMENT - AL1.TOTAL_PAYMENTS as BalAfterInsPymts,
  AL2.EXPECTED_PAYMENT - AL2.TOTAL_PAYMENTS as BalAfterAllPymts,
  AL1.TOTAL_PAYMENTS / AL2.EXPECTED_PAYMENT as OrigRatio ,
  AL1.TOTAL_PAYMENTS / AL2.EXPECTED_PAYMENT as RatioLatest,
  AL2.TOTAL_PAYMENTS - AL1.TOTAL_PAYMENTS as OthPymts,
  AL2.TOTAL_CHARGES - (AL2.NONCOVERED_DC_CHARGES + AL2.NONCOVERED_CT_CHARGES) as CoveredCharges,
  AL2.TOTAL_CHARGES - Sum (AL5.ADJUSTMENT_AMOUNT) as AllowOrig,
  AL2.TOTAL_CHARGES - Sum ( AL5.ADJUSTMENT_AMOUNT) as AllowCurrent,
  AL7.DATE_INTERFACED as DateIdentified,
  AL2.LENGTH_OF_STAY as LOS,
  AL2.DRG_NO as DrgNo,
  MAX ( AL7.PAYMENT_DATE ) as LatestPayorPymtDate,
  AL2.EXPECTED_PAYMENT - (AL2.TOTAL_CHARGES - Sum (AL5.ADJUSTMENT_AMOUNT)) as VarianceCurrent,
  AL2.EXPECTED_PAYMENT - (AL2.TOTAL_CHARGES - Sum (AL5.ADJUSTMENT_AMOUNT)) as VarianceOrig

FROM 
  CV_OCXVW.ACCOUNT_PAYOR AL1,
  CV_OCXVW.CUSTOMER_ENCOUNTER AL2,
  CV_OCXVW.ACCOUNT_TRANSACTION_DETAILS AL5,
  CV_OCXVW.CUSTOMER AL6,
  CV_OCXVW.ACCOUNT_PAYMENT_DETAIL AL7 

WHERE AL2.ENCOUNTER_NO = AL1.ENCOUNTER_NO
  AND AL2.ENCOUNTER_NO = AL5.ENCOUNTER_NO
  AND AL6.CUSTOMER_NO  = AL2.CUSTOMER_NO
  AND AL7.ENCOUNTER_NO = AL1.ENCOUNTER_NO 
  AND AL1.ACCOUNT_ID Not In ('D15','D16','D17')
  AND AL2.EXPECTED_PAYMENT>0
  AND AL5.TRANSACTION_CODE in ('86004','86020','86035','86036','86037')
  AND AL1.RANK=1
  AND AL7.TRANSACTION_CODE in ('47003','47008','47009','47010','47011','47012','47013')
  AND AL7.PAYMENT_DATE IN (
    SELECT AL14.PAYMENT_DATE 
    FROM CV_OCXVW.ACCOUNT_PAYMENT_DETAIL AL14 
    WHERE AL14.TRANSACTION_CODE in ('47003','47008','47009','47010','47011','47012', '47013'))
  AND trunc(AL7.DATE_INTERFACED) = trunc (sysdate) - 1

GROUP BY 
  AL1.ACCOUNT_ID,
  AL1.ACCOUNT_ID,
  AL7.ENCOUNTER_NO,
  AL2.CustType,
  AL6.LAST_NAME,
  AL6.FIRST_NAME,
  AL6.RECORDS_NO,
  AL2.ADMIT_DATE,
  AL2.DISCHARGE_DATE,
  AL2.DATE_BILLED,
  AL2.TOTAL_PAYMENTS,
  AL2.TOTAL_CHARGES,
  AL2.TOTAL_CHARGES,
  AL2.EXPECTED_PAYMENT,
  AL2.EXPECTED_PAYMENT,
  AL1.TOTAL_PAYMENTS,
  AL1.TOTAL_PAYMENTS,
  AL2.EXPECTED_PAYMENT - AL1.TOTAL_PAYMENTS,
  AL2.EXPECTED_PAYMENT - AL2.TOTAL_PAYMENTS,
  AL1.TOTAL_PAYMENTS / AL2.EXPECTED_PAYMENT,
  AL1.TOTAL_PAYMENTS / AL2.EXPECTED_PAYMENT,
  AL2.TOTAL_PAYMENTS - AL1.TOTAL_PAYMENTS,
  AL2.TOTAL_CHARGES - (AL2.NONCOVERED_DC_CHARGES + AL2.NONCOVERED_CT_CHARGES),
  AL7.DATE_INTERFACED,AL7.DATE_UPDATED,
  AL2.LENGTH_OF_STAY,
  AL2.DRG_NO 
HAVING AL2.EXPECTED_PAYMENT - AL1.TOTAL_PAYMENTS >=5000
ORDER BY AL7.DATE_INTERFACED

 

[ddiamond]

I think the following will work with your original query:

MAX ( AL7.PAYMENT_DATE ) as LatestPayorPymtDate,
MIN ( AL7.PAYMENT_DATE ) as FirstPayorPymtDate,
TO_NUMBER(SUBSTR(MIN(TO_CHAR(AL7.PAYMENT_DATE,'YYYYMMDD')||CHAR(AMOUNT)),9)) 
  as AmtFirstPayorPymt,
TO_NUMBER(SUBSTR(MAX(TO_CHAR(AL7.PAYMENT_DATE,'YYYYMMDD')||CHAR(AMOUNT)),9))
  as AmtLastPayorPymt,

 

[ddiamond]

Why are you using the distinct key word when you have a group by clause?

 

[LevelThought]

ddiamond,

Is it not necessary to use the distinct key word when one has a group by clause?

Please clarify.

 

[LevelThought]

Would something like this work?

select AL7.encno,epd.payment_date first_date,epd.pymamt first_date_amt,AL71.paymentdate

last_date,AL71.pymamt last_date_amt
from account_payment_detail AL7, account_payment_detail AL71
where AL7.payment_date = ( select min( payment_date ) from AL7 where encno = AL71.encno )
and AL71.transaction_code in ('86004','86020','86035','86036','86037')
and AL71.paymentdate = ( select max( paymentdate) from epd where encno = AL71.encno )
and AL71.transaction_code in ('86004','86020','86035','86036','86037')
group by AL7.encno, AL7.payment_date, AL7.payment_amount, AL71.payment_date, AL71.payment_amount

 

[ddiamond]

I think you want AL7 instead of epd, but aside from that it should work with this simplified version of your query. The only think to be careful of is that this might through off your sums in your original query. It also may not. You will have to test it, paying careful attention to AllowOrig, AllowCurrent, VarianceCurrent, and VarianceOrig.

Also, you have the condition "and AL71.transaction_code in ('86004','86020','86035','86036','86037')" twice. I think you meant for one of them to be "and AL7.transaction_code in ('86004','86020','86035','86036','86037')

 

[ddiamond]

Level,

As long as all of the expressions in you group by match the expressions in your select clause exactly, a distinct key word is never necessary.

- Dan

 

[LevelThought]

ddiamond,

You indicated that the latest SQL script that I provided might throw off the sums in the original query. Therefore, would your SQL script or the use of "Union" within the query never impact the sums in the original query?

Just thought that I would pose this question before detailed testing.

Thanks in advance.

 

[ddiamond]

My query should not effect the sums. Both your query and the union query could potentially. It all depends on the relationship between AL5 and AL7.