[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Problem with no leading zero when using odbc to extract from oracle

Posted on 2007-10-11
13
Medium Priority
?
1,512 Views
Last Modified: 2013-11-28
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??
0
Comment
Question by:Haveland
  • 5
  • 3
  • 2
  • +3
13 Comments
 
LVL 14

Expert Comment

by:ldunscombe
ID: 20062229
You need to make sure that the fields that you are extracting to are set as text fields.
0
 
LVL 18

Expert Comment

by:sventhan
ID: 20062243
0
 

Author Comment

by:Haveland
ID: 20062288
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...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 14

Expert Comment

by:ldunscombe
ID: 20062304
How do the values display in the linked table ?
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 20062322
0,01  meaning 1/100th?  the , being the decimal mark?

AW
0
 

Author Comment

by:Haveland
ID: 20062327
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
0
 

Author Comment

by:Haveland
ID: 20062330
"," is the decimal mark, yes
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 20062479
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
0
 

Author Comment

by:Haveland
ID: 20062491
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.
0
 
LVL 1

Accepted Solution

by:
Geraint_M_Jones earned 1000 total points
ID: 20063764
What is the column type ? NUMBER ?

What happens if you SELECT your number filed using this code :

TO_CHAR (NUMBERFILED, '9G999D99L', 'nls_numeric_characters = '',.'')
0
 

Author Comment

by:Haveland
ID: 20064215
How do I use that code? In access query design directly, or in SQL+?
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 20064481
"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
0
 
LVL 23

Assisted Solution

by:paquicuba
paquicuba earned 1000 total points
ID: 20065383
Have you tried using a Pass-Through query instead?

In a Pass-Trough query you pass the same SQL you sue in SQL*Plus, so there you can use the TO_CHAR function to format your number, something like TO_CHAR(<numeric column>,'990,00')
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question