How to return decimal value in pass-through query?

[tracy1234]

I'm using an odbc connection from MS Access to return data from an Oracle table. This table contains decimal values ex: 0.5. When I run my query outside of Access in Toad, the decimal values return correctly. But when I run the same query inside Access via a pass through query, the data is returned as 0 instead of 0.5.

This has nothing to do with data type chosen for the field names in the Access tables - I see the 0 value as I'm running the pass-through, before the data is even stored in the Access tables.

How can I modify my pass-through so that decimal values are returned?

Simplified query:

Select
quantity_ordered, --may be decimal
quantity_delivered, --may be decimal
quantity_ordered - a.quantity_delivered)"not delivered", --may be decimal
from mytable;

 

[scriverb]

Try using the Format function:
Select
Format(quantity_ordered, "0.0&quot, --may be decimal
Format(quantity_delivered, "0.0", --may be decimal
Format((quantity_ordered - a.quantity_delivered), "0.0&quot as Not_Delivered, --may be decimal
from mytable;

Let me know if this works for you. Bob Scriver

 

[tracy1234]

Bob-
I just tried that and my odbc connection returns a failure. Tried using "Format" in Toad while connected to Oracle and it returns an invalid column name. So I don't think "Format" is going to work.


-Tracy

 

[tracy1234]

I have solved this on my own and am posting for those who need the answer:

select
substr(to_char(a.quantity_ordered),1,10),
substr(to_char(a.quantity_delivered),1,10),
substr(to_char(a.quantity_ordered - a.quantity_delivered),1,10) "not delivered"
from mytable;

This works for a pass-through query into Oracle using an odbc connection.

 

[scriverb]

I am sorry Tracy1234 I had intended to have you take your pass-through query into an ACCESS query and use the Format Functions there. This allows you to use all of the ACCESS functions within the ACCESS query to manipulate and format the data as you wish.

Just create a new ACCESS query with the pass-through as its recordsource. Then that will work for you. Your solution works also but sometimes you still have to manipulate the data on the ACCESS side and I have found this technique very useful. Bob Scriver

 

[tracy1234]

Bob-

Thanks very much for your continued input on my problem. I have experimented as you suggested with the format function. But if I don't use substr(to_char(),,) in the pass-through itself, I get 0.0 as my pass-through is still returning a 0.

On the bright side, I have looked up the functions available to me and now know how I can use them in the future.

-Tracy