Invalid Data Precision - SQL Pass Through Query in Access 5

[LevelThought]

Not able to use the following SQL pass-through query within 2003 Microsoft Access to extract data from an Oracle 8.1.7 database!

SELECT distinct A1.ACCOUNT_ID, A2.ACCOUNT_NO, trunc(A2.DATE_INSERTED)
FROM ACCOUNT_PAYOR A1, ACCOUNT_PAYMENT_DETAIL A2
WHERE A1.ACCOUNT_NO = A2.ACCOUNT_NO
AND A1.ACCOUNT_ID = 'C24'

The error that I receive is "Invalid precision for decimal data type." This appears to be related to the use of "trunc" on the "date inserted" field!

Any idea as to the solution to this problem?

Thanks in advance.

 

[PHV]

As you use a pass-through query, it's much likely an Oracle question than a MS-Access one ...

 

[LevelThought]

I can use "trunc" within the Brio(Hyperion) environment as well as in the SQL Plus environment to extract data from the Oracle database.

Why would this not work within an Access pass-thru query to extract data from an Oracle database?

 

[cmmrfrds]

Does the query run okay on the Oracle side, such as, Toad or some other client software?

If so it is probably in the ODBC mapping, you could try formatting it as a date.
to_char(trunc(A2.DATE_INSERTED),'MM/DD/YYYY')

 

[LevelThought]

The query works fine in SQL Plus using the trunc(A2.Date_Inserted).

Using "to_char(trunc(A2.DATE_INSERTED),'MM/DD/YYYY')" results in the same error - Invalid precision for decimal data type.

 

[cmmrfrds]

What ODBC driver are you using? Microsoft's or Oracle's.

Maybe try the one you are not using now.

Will the query return the date without using the Trunc function?

It might be interesting to loop through the fields collection and see what data type is being returned.

Put where 1<>1 in the where clause so that it does not return any data just the meta data like the fields collection and then look at the data type.

 

[LevelThought]

Initially, I used the "MicrosoftODBC for Oracle" driver. Then, I used the "Oracle in OraHome9i" driver.

Still, I receive the error message, "Invalid Data Precision."

If I don't use the "trunc" function, the dates are returned similar to "11/15/2005 5:40:00 AM."

Also, I did put "1<>1" in the where clause.

What are the steps to look for the data type?


I assume that one can use ADO to append data from Oracle to a local Access table.

 

[cmmrfrds]

To look through the fields collection on an ADO recordset. Example.
Dim sql1 as String
Dim rs as New ADODB.Recordset

sql1 = "select * from yourtable where 1 <> 1"
rs.Open sql1, cn, adOpenStatic, adLockOptimistic

Dim fl As ADODB.Field, indx As Integer
For Each fl In rs.Fields
Debug.Print "The Field Name = "; rs.Fields(indx).Name
Debug.Print "The Field Type = "; rs.Fields(indx).type
indx = indx + 1
Next

The type will be shown as a constant so you need to look through the ADODB DataTypeEnum. A 5 is a double, a 134 is a datetime, etc....

Sure, you can add records from an ADO recordset to an Access table.

Depending on how big the table is an strategic the app is, an option is to have the Oracle DBA make the Access table a linked server. Then the query would run on the Oracle side and update the Access table.

Possibly another thing to try is convert to character and then back to date and avoid the trunc function.

to_date(to_char(A2.DATE_INSERTED,'MM/DD/YYYY'),'MM/DD/YYYY')

 

[cmmrfrds]

I looked at some code that I used before and this format worked for me in a query.

to_date(to_char(sysdate-6, 'MM/DD/YYYY'))

Another thing.
If you make an ADO connection and avoid ODBC altogether do you still get the error. As in the example I showed for the fields collection.

 

[LevelThought]

The error persists! The SQL script is displayed below:

I will try an ADO connection...

SELECT DISTINCT AL1.CONTRACT_ID, AL1.CONTRACT_ID, AL7.ENCOUNTER_NO, AL2.PATIENT_TYPE, AL6.LAST_NAME, AL6.FIRST_NAME, AL6.MEDICAL_RECORDS_NO, AL2.ADMIT_DATE, AL2.DISCHARGE_DATE, AL2.DATE_BILLED, AL2.TOTAL_PAYMENTS, AL2.TOTAL_CHARGES, AL2.TOTAL_CHARGES, AL2.EXPECTED_REIMBURSEMENT, AL2.EXPECTED_REIMBURSEMENT, AL1.TOTAL_PAYMENTS, AL1.TOTAL_PAYMENTS, AL2.EXPECTED_REIMBURSEMENT - AL1.TOTAL_PAYMENTS, AL2.EXPECTED_REIMBURSEMENT - AL2.TOTAL_PAYMENTS, AL1.TOTAL_PAYMENTS / AL2.EXPECTED_REIMBURSEMENT, AL1.TOTAL_PAYMENTS / AL2.EXPECTED_REIMBURSEMENT, AL2.TOTAL_PAYMENTS - AL1.TOTAL_PAYMENTS, AL2.TOTAL_CHARGES - (AL2.NONCOVERED_PC_CHARGES + AL2.NONCOVERED_CD_CHARGES), AL2.TOTAL_CHARGES - Sum (AL5.ADJUSTMENT_AMOUNT), AL2.TOTAL_CHARGES - Sum ( AL5.ADJUSTMENT_AMOUNT), AL7.DATE_INSERTED,AL7.DATE_UPDATED, AL2.LENGTH_OF_STAY, AL2.DRG_NO, MAX ( AL7.PAYMENT_DATE ), AL2.EXPECTED_REIMBURSEMENT - (AL2.TOTAL_CHARGES - Sum (AL5.ADJUSTMENT_AMOUNT)), AL2.EXPECTED_REIMBURSEMENT - (AL2.TOTAL_CHARGES - Sum (AL5.ADJUSTMENT_AMOUNT)), AL2.EXPECTED_REIMBURSEMENT - ( AL2.TOTAL_CHARGES - Sum ( AL5.ADJUSTMENT_AMOUNT ) ) FROM FV_SACW.ENCOUNTER_PAYOR AL1, FV_SACW.PATIENT_ENCOUNTER AL2, FV_SACW.ENCOUNTER_TRANSACTION_DETAILS AL5, FV_SACW.PATIENT AL6, FV_SACW.ENCOUNTER_PAYMENT_DETAIL AL7 WHERE ( AL2.ENCOUNTER_NO = AL1.ENCOUNTER_NO AND AL2.ENCOUNTER_NO=AL5.ENCOUNTER_NO AND AL6.PATIENT_NO=AL2.PATIENT_NO AND AL7.ENCOUNTER_NO=AL1.ENCOUNTER_NO) AND (AL1.CONTRACT_ID Not In ('AB4','AB5','AB6'))
AND AL2.EXPECTED_REIMBURSEMENT>0 AND AL5.TRANSACTION_CODE in ('47004','47005','47006') AND AL1.RANK=1 AND AL7.TRANSACTION_CODE in ('5470','5471') AND AL7.PAYMENT_DATE IN (SELECT AL14.PAYMENT_DATE FROM FV_SACW.ENCOUNTER_PAYMENT_DETAIL AL14 WHERE (AL14.TRANSACTION_CODE in ('5470','5471'))) AND
to_date(to_char(AL7.DATE_INSERTED,'MM/DD/YYYY')) = '12/01/2005'
GROUP BY AL1.CONTRACT_ID, AL1.CONTRACT_ID, AL7.ENCOUNTER_NO, AL2.PATIENT_TYPE, AL6.LAST_NAME, AL6.FIRST_NAME, AL6.MEDICAL_RECORDS_NO, AL2.ADMIT_DATE, AL2.DISCHARGE_DATE, AL2.DATE_BILLED, AL2.TOTAL_PAYMENTS, AL2.TOTAL_CHARGES, AL2.TOTAL_CHARGES, AL2.EXPECTED_REIMBURSEMENT, AL2.EXPECTED_REIMBURSEMENT, AL1.TOTAL_PAYMENTS, AL1.TOTAL_PAYMENTS, AL2.EXPECTED_REIMBURSEMENT - AL1.TOTAL_PAYMENTS, AL2.EXPECTED_REIMBURSEMENT - AL2.TOTAL_PAYMENTS, AL1.TOTAL_PAYMENTS / AL2.EXPECTED_REIMBURSEMENT, AL1.TOTAL_PAYMENTS / AL2.EXPECTED_REIMBURSEMENT, AL2.TOTAL_PAYMENTS - AL1.TOTAL_PAYMENTS, AL2.TOTAL_CHARGES - (AL2.NONCOVERED_PC_CHARGES + AL2.NONCOVERED_CD_CHARGES), AL7.DATE_INSERTED,AL7.DATE_UPDATED, AL2.LENGTH_OF_STAY, AL2.DRG_NO HAVING AL2.EXPECTED_REIMBURSEMENT - (AL2.TOTAL_CHARGES - Sum (AL5.ADJUSTMENT_AMOUNT))>100
ORDER BY 26

 

[jsteph]

Level,
Have you tried aliasing the columns with functions?
Instead of
Select Trunc(somefield) From SomeTable
Try
Select Trunc(somefield) as truncdfield From Sometable
--Jim

 

[cmmrfrds]

Or, any of the calculated columns in the select list.
For example.
AL2.TOTAL_CHARGES - (AL2.NONCOVERED_PC_CHARGES + AL2.NONCOVERED_CD_CHARGES)
Oracle will probably generate this as the column name.
(AL2.TOTAL_CHARGES - (AL2.NONCOVERED_PC_CHARGES + AL2.NONCOVERED_CD_CHARGES))
Better to have a simple name such as CoveredCharges

 

[LevelThought]

Finally !!! I was able to successfully obtain data!

I guess that the aliasing did the trick.

Thanks for the insight!

How would I modify the statement so that the results from the Passthrough Query is appended to my local access table?

Ideally, I would like to append data to the local access table manually at first and then set this up so that the update is done automatically.

Thanks in advance.

 

[LevelThought]

Further,

Currently, I manually change the AL7.Date_Inserted value every day.

Any way I can have the passthrough query prompt me for a date to enter for this field?

WHERE (AL14.TRANSACTION_CODE in ('5470','5471'))) AND
trunc(AL7.DATE_INSERTED) = '12/01/2005'

 

[jsteph]

For append-to-local with passthru, you could drag the saved passthru into a new query and make it an append.

You can also drag it to a new query and use parameters.

Be aware that performance greatly suffers for these parameter querys--JET will first read the entire dataset from the raw passthru, then filter the JET query by the parameter.

The best way to use a passthru when you have changing criteria is to build it on the fly--save the 'base' select statement somewhere, (like a dummy query, for instance, or even in a code module as a function that returns the sql string), then use the base and dynamically build the WHERE clause for each instance.
--Jim

 

[LevelThought]

Could I create a passthru query within a code module using ADO to append to the local Access table on a daily basis? Each and every day, I would like to load all records that had a DateInserted = trunc(sysdate).

Any examples?

Also, what about setting up a passthru query to append to a local Access table using Windows Scheduler or when the local Access database is accessed every morning?

 

[jsteph]

If you're using ADO, you might want to create a stored procedure to which you can pass parameters.

Then set an ado.recordset to that proc's return, loop through the recordset and append to local. This gives you more flexibility if any other local logic is needed in the append procedure, also it's easier to debug--if a query fails, as you've seen, often the error message doesn't pinpoint the record or field that's causing the problem, where with a procedure you can carefully step through everything and handle different possible error situations.

Another option is to have the sp load a temp table (identical to the local table) on the server, then upon completion of the sp, do a simple append from the (odbc-linked) temp table.

If you go the ado.recordset route, you'd put your sql in a sp, have a param for dateinserted, this would be the WHERE clause, and the sp returns a recordset. It's been years since I did Oracle PLSql, so I can't remember if you need to do anything special to make the sp return a recordset.
--Jim

 

[appelq]

Just my 2 cents:
Why not let the date come over without the Trunc function in the pass through query and clean it up inside Access with a
FORMAT(A2.DATE_INSERTED, "Short Date") AS DateInserted?

 

[LevelThought]

thanks for the insight.

If you wanted to automate the appending of the Oracle data to the local Access table either at a set time every day or upon the opening of the main form in the database, what would you use - Passthru using ADO or some other method?

Just trying to ensure that I design this optimally at the outset instead of waiting down the road for some issues that I am not currently aware of.

 

[jsteph]

Whether you do it on opening a form or at a set time depends on the nature of the data. I tend to not like to do things like that on the load of a form. If it has to be done immediately prior to a users going into the system, my preference is to get the form open so the user can look at something (maybe a status box), and then run the process. You could do that on a timer event, say .5 sec, then the first line of code in the timer is to disable it (Interval = 0), then fire off the load. Make sure you disable buttons that do things that could affect the incoming data.

If it's bulk loading I prefer to do any complex sql on the server and dump it into a temp table on the server, since any calculations or complexities can be done where there's (usually) more horsepower, then when that completes you do the simple append from server to local--here you can use an odbc-linked table (the temp table on the server) to append to the local table.

If the append was, say, a subset of millions of records, and there was complex logic, grouping, joins etc, (I didn't look close at your sql so I'm not sure what all was involved there) then the user has a free client system while all that nastyness is going on at the server, and the final append is a simple, straightforward schlepping of data. Plus any errors, etc, are isolated--an error in the initial gathering as opposed to an error moving the data to access--if it was all done in one fell swoop, it could be harder to diagnose those ambiguous error messages.
--Jim