Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    54

    Unanswered: Insert statement - works, yet data isn't exactly right....

    I'm trying to insert some decimal numbers and for some reason the DB is only capturing the integer part and not the decimals.

    Here is the insert statement:

    Code:
    INSERT INTO AOFDB01.TIVOLI_SRVR_MGMT_STAT (TIVOLI_SRVR_MGMT_STAT_ID,
    CLLCTN_EVNT_TXT, NOD_NM, SYS_NM, PLTFRM_NM, CLNT_OS_LVL_NM,         
    PLCY_DOMN_NM,                                                       
    CLNT_VRSN_NBR, CLNT_RELS_NBR, CLNT_LVL_NBR, CLNT_SUB_LVL_NBR,       
    UNIFM_RSRC_LOCATOR, SCHD_NM, RSLT_TXT, INSPT_NBR, OBJ_BKUP_NBR,     
    OBJ_DEL_NBR, OBJ_UPD_NBR, OBJ_FAILED_NBR, OBJ_REBOUNDED_NBR,        
    GB_XFER_NBR, DATA_XFER_TM_NBR, CMPU_ELPS_TM_NBR, NTWK_RT_NBR,       
    AGGRT_RT_NBR, OBJ_CPRSD_NBR, OBJ_EXP_NBR)  VALUES (42486669,        
    'RESULTS LATE', 'PRCHSXTSMMGT02', 'TSM_WAU05', 'AIX', 'VER5.3',     
    'AIX_DISK', 05, 01, 05, 015, '?', '1505', 'COMPLETED', 051771,      
    0120, 00, 00, 00, 00, DECIMAL(1.0000010,7), 1.0000496, '00:13:24',  
    DECIMAL(1.8020815,8), 0, 00, 08);
    When I select that record (either with crystal reports or spufi), all of the fields have a result of 1 and don't contain their decimals.

    As you can see in the query, I have tried to use the decimal scalar function to try and make it 7 or 8 decimal places and in other cases, just left th enumber as-is.

    Where am I going wrong?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What is the data type of the columns in the table?

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think you should check the manual to see what DECIMAL(1.0000010,7) really means.

  4. #4
    Join Date
    Nov 2004
    Posts
    54
    Did some reading and just trying a bunch of things. Clearly, I'm not as strong in DB2 as I could be.



    Anyway, according to the DDL which was used by the DBAs to create the tables, it has a data type of DECIMAL.

    Code:
    CREATE TABLE TIVOLI_SRVR_MGMT_STAT
    (
     TIVOLI_SRVR_MGMT_STAT_ID  INTEGER NOT NULL, 
     CLLCTN_EVNT_TXT       VARCHAR(16) NOT NULL, 
     NOD_NM                VARCHAR(64), 
     SYS_NM                VARCHAR(32), 
     PLTFRM_NM             VARCHAR(32), 
     CLNT_OS_LVL_NM        VARCHAR(32), 
     PLCY_DOMN_NM          VARCHAR(32), 
     CLNT_VRSN_NBR         SMALLINT, 
     CLNT_RELS_NBR         SMALLINT, 
     CLNT_LVL_NBR          SMALLINT, 
     CLNT_SUB_LVL_NBR      SMALLINT, 
     UNIFM_RSRC_LOCATOR    VARCHAR(128), 
     SCHD_NM               VARCHAR(32), 
     RSLT_TXT              VARCHAR(32), 
     INSPT_NBR             INTEGER, 
     OBJ_BKUP_NBR          INTEGER, 
     OBJ_DEL_NBR           INTEGER, 
     OBJ_UPD_NBR           INTEGER, 
     OBJ_FAILED_NBR        INTEGER, 
     OBJ_REBOUNDED_NBR     INTEGER, 
     GB_XFER_NBR           DECIMAL, 
     DATA_XFER_TM_NBR      DECIMAL, 
     CMPU_ELPS_TM_NBR      VARCHAR(32), 
     NTWK_RT_NBR           DECIMAL, 
     AGGRT_RT_NBR          DECIMAL, 
     OBJ_CPRSD_NBR         INTEGER, 
     OBJ_EXP_NBR           INTEGER, 
     END_TS                TIMESTAMP, 
     BEG_TS                TIMESTAMP
    );
    Quote Originally Posted by n_i View Post
    I think you should check the manual to see what DECIMAL(1.0000010,7) really means.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    " NTWK_RT_NBR DECIMAL, " is equivalent to " NTWK_RT_NBR DECIMAL (5,0)," that is, it has 0 scale. Your query results are exactly what you should expect.

  6. #6
    Join Date
    Nov 2004
    Posts
    54
    Sounds to me that the creation of the tables was incorrect and should have been:

    ntwk_rt_nbr decimal(5,8)

    D-

    Quote Originally Posted by n_i View Post
    " NTWK_RT_NBR DECIMAL, " is equivalent to " NTWK_RT_NBR DECIMAL (5,0)," that is, it has 0 scale. Your query results are exactly what you should expect.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Scale obviously cannot be greater than precision. "I want my decimals have 5 digits, including 8 in the fractional part". May be it's time to refer to the manuals...

  8. #8
    Join Date
    Nov 2004
    Posts
    54
    RTFM - awesome reply!

    Quote Originally Posted by n_i View Post
    Scale obviously cannot be greater than precision. "I want my decimals have 5 digits, including 8 in the fractional part". May be it's time to refer to the manuals...

Posting Permissions

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