Haveland
asked on
Problem with no leading zero when using odbc to extract from oracle
I'm trying to extract some information from an oracle database via odbc. In one of the fields it seems like figures are displayed without leading zero. When I use Sql + and make select queries I see that figures like 0,01 are displayed as ,01 without leading zero. If I use odbc to extract those figure to Ms Access, I get 1. What causes this, and how can I get the correct value (0,01) to show up in Ms Access??
ldunscombe
You need to make sure that the fields that you are extracting to are set as text fields.
The leading zeros should be there, please use the correct format mask.
https://www.experts-exchange.com/questions/21778160/Leading-ZEros.html?eeSearch=true
https://www.experts-exchange.com/questions/21778160/Leading-ZEros.html?eeSearch=true
ASKER
I don't understand what you mean. I have linked the table into Ms Access via an odbc and want to build a query where I can see the leading zero...
How do the values display in the linked table ?
0,01 meaning 1/100th? the , being the decimal mark?
AW
AW
ASKER
A value that should display 0,01 is displayed as 1
If I use SQL+ and run a query against the same table, I see the value as ,01
If I use SQL+ and run a query against the same table, I see the value as ,01
ASKER
"," is the decimal mark, yes
The presence or absence of the 'leading' 0 is really only important when the data ia actually displayed (in a form or a report), so you should only worry about it at that point. Don't obsess over the format of the value in the result set of the query.
AW
AW
ASKER
actually, it's used to make some calculations for reports and as it is now it becomes wrong since I get 1 instead of 0,01 as a multiplier.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How do I use that code? In access query design directly, or in SQL+?
"How do I use that code? In access query design directly, or in SQL+?"
either way. This is part of a standard SQL statement:
Select TO_CHAR (YOUR_NUMBERFIELD, '9G999D99L', 'nls_numeric_characters = '',.'') FROM YOUR_TABLE
AW
either way. This is part of a standard SQL statement:
Select TO_CHAR (YOUR_NUMBERFIELD, '9G999D99L', 'nls_numeric_characters = '',.'') FROM YOUR_TABLE
AW
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.