| Error "Invalid precision for decimal data type" [message #101167] |
Tue, 01 June 2004 08:11  |
sdmcnitt
Messages: 2
Registered: June 2004
|
Junior Member |
|
|
I am working with Microsoft ODBC for Oracle (msorcl32.dll 2.573.6200) in a pass-thru query in Access 2000 SR-1. I have the client software for Oracle 9i.
I keep getting "Invalid precision for decimal data type" in ODBC but this does not happen in SQL*Plus. In playing around with the query, some interesting (and frustrating) things are found. For example:
--this works!!
SELECT ID_NUMBER, nvl(CLN_COMMIT_FEES, 0)
FROM SOME_TABLE
WHERE UD_DATA_SOURCE = 'CLC'
AND AS_OF_DATE = TO_DATE('03/31/2004','mm/dd/yyyy')
--but this doesn't??? WTF?
SELECT CL.ID_NUMBER,
nvl(CL.CLN_COMMIT_FEES, 0)
FROM SOME_TABLE CL
WHERE CL.UD_DATA_SOURCE = 'CLC'
AND CL.AS_OF_DATE = TO_DATE('03/31/2004','mm/dd/yyyy')
The fees column is NUMBER(14,2)
Again, they both work in SQL*Plus so I am concentrating on ODBC issues. I am going to request Office SP-3 first.
|
|
|
|
|
|
|
|
|
|
| Re: Error "Invalid precision for decimal data type" [message #144479 is a reply to message #101167] |
Wed, 26 October 2005 13:07  |
fox3c
Messages: 1
Registered: October 2005
|
Junior Member |
|
|
Don't know if anyone still needs this. But one possible solution is to bracket your "compound" column and rename it, for example in your query:
SELECT CL.ID_NUMBER,
(nvl(CL.CLN_COMMIT_FEES, 0)) as NewField
FROM SOME_TABLE CL
or some such. Access gets confused easily, you have to be careful how you bracket (or don't bracket things), how you use double and single quotes etc. In this case it likely got confused over the data type coming from the nvl function. I've seen the same thing happen with simple multiplications, concatenations, or other functions when Access has trouble converting the result of the calculation or function into one of its own datatypes. Hope this helps anyone who needs it.
[Updated on: Wed, 26 October 2005 13:12] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
