Complex Passthru Query - Append & Update Access Table 2

[LevelThought]

Need some insight,

Ideally, one should not store calculated values in an Access table. However, at the request of Management, I have no choice! Therefore,based on extensive reading, it appears that the most efficient method is as follows:

Append process

Step 1: Create Passthru query
Step 2: Insert results of query into local Access table

Update process

Step 1: Create Passthru Union query
Step 2: Insert results into temp table
Step 3: Using an inner join from the temp table to my local Access table, update fields on those accounts that had a payment interfaced the Oracle Application the prior day as recorded in a Oracle table, or had a change in expected payment as recorded on another Oracle table.
Step 4: Clear out the temp table
Step 5: Setup to run automatically using Windows Scheduler.

Now, here is the tricky part - Is it possible to have a subquery on a passthru query so that I can retrive the following on a daily basis?:

Date of Latest Payor Payment
Amt of Latest Payor Payment

Any additional comments would be greatly appreciated?

Thanks in advance.

 

[JerryKlmns]

An SQL specific query sends commands directly to an SQL database server such as your Oracle. So ... subquery question is for Oracle to answer. Tried anything? And a thought: Why don't you do all that in Oracle and just get the final result into MSAccess? Wouldn't it be faster?

 

[Crowley16]

I've not used oracle, but in Sql Server you can actually set up a calculated field on a table and for all it'll behave pretty much like a normal field on the table...

I'm just guessing but, most likely you can do something similar in oracle...

--------------------
Procrastinate Now!

 

[LevelThought]

Thanks for the information.

However, I am not aware of how one does this in Oracle and "push" this to the local Access tables on a daily basis.

Are you referring to Oracle stored functions, procedures, or Analytics? A linked server table?

Can you explain in more detail?


If you had the following Passthru SQL, how would you do this in Oracle considering that you need to append to the local Access table all accounts that had a payment interface the Oracle table 1 day before and update 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?

Over the last 6 months, I have explored the following methods; Access Query, ADO Code, Crosstab Queries, and SQL Passthru Queries.

Note, the SQL script below "pulls" the wrong value for the "LatestPayorPymtDate." The "AllowOrig" and "AllowCurrent" fields might be wrong as well!

If a SQL Passthru query is to be used, how can the one below be tweaked to accurately "pull" the following?:

AmtFirstPayorPymt
DateFirstPayorPymt
AmtLastPayorPymt
DateLastPayorPymt
LatestPayorPymtDate
AllowOrig
AllowCurrent


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.

 

[JerryKlmns]

LevelThought

Oracle is not my field. Nor is this forum.
Since Oracle is a REAL RDBMS on a strong machine, you should take advantage of that and do the process there.I don't know how.

BTW thnx 4 the *