DECIMAL scalar function?

chrischris · #1 (**permalink**) 03-07-07, 14:40

I know this topic may already have been discussed but the links on the page returned by the search tool were not working so i decided to post my simple question instead.

I don't really know anything about DB2 (im not even sure if im in the correct forum) but I'm quering data from an IBM i5 server using visual basic 6 and ADO. I have got it to work fine the only problem is that i cant get decimal type data to return as a decimal. I use a program called Query, made by SPSS, to find the data type and size of fields in a table. According to the Query program, the field i am trying to get is of type Decimal and has a size of 15 and scale of 2. So I tried using the DECIMAL scalar function to return the field in decimal form but instead of getting 123.45 I get 12345 (no decimal).

Here is the code i use to connect to our server:

Code:

Global gConn As New ADODB.Connection

gConn.Provider = "IBMDA400"
gConn.Properties("Force Translate") = 0
gConn.Open "Provider=IBMDA400;Data Source="MyServer", "User","Password"

Here is the code i use to query the data:

Code:

Dim rs As ADODB.Recordset
Dim Parms As Variant
....
strSQL = "SELECT DECIMAL(LIPQOH,15,2) From DLFDTA57.F41021 F41021"
Set rs = gConn.Execute(strSQL, Parms, -1)
MyString = rs.GetString(adClipString)

The field LIPQOH never returns as a decimal, I've tried various values for the precision and scale parameters of the DECIMAL function but none return the field as a decimal. Am i using the funtion incorrectly? Should i be using a different function? Or even worse am i in the wrong forum?

I would greatly appreciate any help...

grofaty · #2 (**permalink**) 03-08-07, 00:49

chrischris, don't know if this is the best solution but divide returned number by 100. Sample: 12345 / 100 = 123.45. It is not the most elegant solution but thats the way we solved very similar problem of getting data out of non-DB2 database.
Hope this helps,
Grofaty

Wim · #3 (**permalink**) 03-08-07, 05:55

Alter your db2cli.ini file so that it starts like this:

Code:

; Comment lines start with a semi-colon.

[COMMON]
PATCH2=15

Make sure there is only one db2cli.ini file present! Sometimes the installer adds multiple files if you install a new fixpack.

n_i · #4 (**permalink**) 03-08-07, 10:25

Quote:

Originally Posted by chrischris

Here is the code i use to query the data:

Code:

strSQL = "SELECT DECIMAL(LIPQOH,15,2) From DLFDTA57.F41021 F41021"
Set rs = gConn.Execute(strSQL, Parms, -1)
MyString = rs.GetString(adClipString)

Well, if you are accessing it in VB as a string, may be you should not retrieve it as a decimal... Try using CHAR() instead of DECIMAL()

chrischris · #5 (**permalink**) 03-08-07, 12:23

Thank you for all the info!

Someone on another forum told me to multiply the field by .01 ex:
SELECT LIPQOH*.01 From DLFDTA57.F41021 F41021

This did the trick.

Thanks again for the replies

Wim · #6 (**permalink**) 03-09-07, 06:59

I can't believe you want to rely on this kind of solution !?

Quote:

SELECT LIPQOH*.01 From DLFDTA57.F41021 F41021

We had the same problem, the comma disappeared when we retrieved decimals from DB2. 1.0000 would become 10000, 12.20 would become 1220 etc. By applying the PATCH, as I stated before, the problem went away.

But off coarse if you want to keep programming things as

Code:

select field1 * 0.01 as field1,
         field 2 * 0.00001 as field2
from ....

please, be my guest.
By simply applying the patch to your db2cli.ini file you could write things like

Code:

select field1,
         field 2
from ....

(as God intended) but who would want that as a solution ?

Sorry if I bite, but I can't see why you want to make your life miserable and my back hurts as hell (probably the largest contributor to my biting state of mind, so again, sorry if i bite).

chrischris · #7 (**permalink**) 03-09-07, 11:46

Wim,

haha, I derserve that and I know the solution im using is not very eloquent but Im a complete newbie with DB2, I basically know nothing about it. I only know a little about VB and a little about SQL. I would love to try to implemnent you're solution but I have no clue what a db2cli.ini file is, let alone where its located or how to change it. If i change that file is there a chance i can cause some data corruption in the system? Are there any resources you can point me to where i can familiarize myself with this topic? We also use Rumba/JD Edwards on our system, not sure if that has anything to do with what we are talking about though.

Thanks again for the help.

Peter.Vanroose · #8 (**permalink**) 03-10-07, 09:36

Try this:

Code:

SELECT FLOAT(LIPQOH) From DLFDTA57.F41021

The "reason" for the strange behaviour of the DECIMAL datatype is essentially a performance issue: any programming language that supports DECIMAL internally represents it as a digit string (without the decimal point) together with the scale (2 in your case). DB2 does the same, and does not convert this (by inserting a decimal point or decimal comma or whatever) unless you explicitly ask it to do so, e.g. by converting it to CHAR (as n_i suggested) or maybe also to FLOAT (just try it -- I'm not sure).

Internet Services

Web Development

Digital Marketing

Consumer Tech