Access Pass Through Query rounding off ending zeros
We are using MS Access 2010 to run some SQL pass through queries against our iSeries DB2 db. While it is working really well and provides a nice front end to DB2 it's not without it's quirks which may end up being a deal breaker for using Access. For some reason Access is dropping/rounding off zeros at the end of our cost field.
select decimal(fficst, 10, 2) as cost
from mytable
or
select cast(fficst as decimal (10, 2))
from mytable
results in :
20 instead of 20.00
10.5 instead of 10.50
1.4 instead of 1.40
Running the code directly on the iSeries gives the correct results so I'm comfortable the SQL is good. The field can be formatted in a report to display properly, but it would be nice to just see the result display correctly according to the SQL.
select decimal(fficst, 10, 2) as cost
from mytable
or
select cast(fficst as decimal (10, 2))
from mytable
results in :
20 instead of 20.00
10.5 instead of 10.50
1.4 instead of 1.40
Running the code directly on the iSeries gives the correct results so I'm comfortable the SQL is good. The field can be formatted in a report to display properly, but it would be nice to just see the result display correctly according to the SQL.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You probably don't need to do pass through queries at all. I would always start with an Access query against linked tables. If that is slow and the query contains one or more joins, I would create a view. Of course if you are creating a reporting app, you don't n to worry about keeping your queries updateable so pass through queries and stored procedures are also options but I suggest always starting with the "Access way" as the path of least resistance.
ASKER
The article Use Microsoft Access to interact with your DB2 data gave me the idea to give Access a try. The biggest problem I've experienced was finding an inexpensive front end that I can manipulate to do most everything that I want with DB2 SQL.
An excellent product for working with DB2 directly on the iSeries is File Access from Oasis Software International. While we own a license and it is unbelievably easy and useful to use, I find that it has limitations.
While I am hitting some bumps with DB2 Pass Through queries in Access I haven't found any show stoppers like I have in iSeries Nav Run SQL Script and I find STRSQL a little cumbersome. Also, I have some experience with VBA as well so Macros and Email functionality is readily available.
This gives me the result that I'm looking for. I can now easily push my results out to whatever I want...
Open in new window
I'm not really sure who to award the points to.