Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Posts
    5

    Unanswered: DECIMAL scalar function?

    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...
    Last edited by chrischris; 03-07-07 at 18:01.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    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

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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()
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Mar 2007
    Posts
    5
    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

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I can't believe you want to rely on this kind of solution !?
    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).
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Mar 2007
    Posts
    5
    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.

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •