Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Error converting data type nvarchar to decimal - Oracle to SQL Server

  1. #1
    Join Date
    Mar 2017
    Posts
    3

    Default Error converting data type nvarchar to decimal - Oracle to SQL Server

    I'm attempting to convert data tables from an Oracle 11g database to a SQL Server 2012 data warehouse. There are Oracle Number fields which have decimals with a scale of 40 digits to the right of the decimal place. Therefore I'm using Float(53) fields in SQL server.

    I can see that the Table Input is pulling in the Number data as a BigNumber. When I attempt to connect to the Table Output and run the transformation I'm getting the above listed error. There are no string variables involved so I don't understand what is happening. I inserted a Select Values step and attempted to again specify the field type, but that didn't make any difference.

    I'm using the community version of Kettle 4.4.0. I have an oracle 11g client on the PC I'm using for the transformation. I'm using JDBC connections with the most recent jar files I could find. (ojdbc6_g, sqljdbc42) Java 8 update 111 is installed on the PC.

    If you have any thoughts regarding what's happening and how to fix it I would be very grateful.

    rdykstra53

  2. #2
    Join Date
    Mar 2017
    Posts
    3

    Default Revised question

    I've made some progress regarding this issue on my own. Currently I'm working with loading data into a SQL Decimal type instead of a Float because I found out that was not the appropriate type to use for a financial system.

    I've been able to load up to 17 decimal points by using the Pentaho Number data type rather than the BigNumber data type. Every time I use the BigNumber data type I end up with the original 'Error converting data type nvarchar to decimal' error. I found out that Pentaho converts all numbers to strings in order to use the masking formats which accounts for the nvarchar message.

    Does anybody have a way around this issue to enable loading additional decimal points to a SQL Server database?

    rdykstra53

  3. #3
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Perhaps post a simplified version of what it is you are trying to do, and we can provide input.
    With what you have provided, we're going to be guessing at best.

    Eg.
    Table Input -> Table Output

    TI:
    Select
    OraNum as valueToMove
    from
    sourceTable

    TO:
    valueToMove mapped to valueToMove
    Schema: DestinationSchema Table: DestinationTable

    Attaching the KTR of this sample is best.

  4. #4
    Join Date
    Mar 2017
    Posts
    3

    Default Further clarification

    I attached a simple version of the ktr.

    Using Pentaho Community version 4.4 on PC with Java 8 update 111

    Table Input - JDBC connection: ojdbc6_g.jar
    "SELECT MPO_TRANSACTION_FACTOR FROM MIC_POLICY_EG.MIS_POLICIES"
    (Retrieving Oracle Number variable with 40 decimal points)

    Select Values - Select & Alter: MPO_TRANSACTION_FACTOR
    Meta-data: Fieldname : MPO_TRANSACTION_FACTOR
    Rename to : TRANSACTION_FACTOR
    Type : BigNumber
    Length : 38
    Precision : 37
    Format : #.#
    Decimal : .

    Table Output: Connection JDBC (sqljdbc42)
    Mapping: TRANSACTION_FACTOR to MPO_TRANSACTION_FACTOR in SQL Server database (Decimal (38,37))

    No matter what size I set the BigNumber to, I get the following error result:
    "Error converting data type nvarchar to decimal"

    If I change the BigNumber to Number Length 20 Precision 17 I successfully get 17 decimals.
    Attached Files Attached Files
    Last edited by rdykstra53; 03-14-2017 at 04:53 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.