Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Spoon interpreting field wrong

  1. #1
    Join Date
    Sep 2007
    Posts
    13

    Default Spoon interpreting field wrong

    I am loading data from a file off of my AS400. This files is very generic. Spoon sees one of my fields as numeric when I want it to be a STRING. Since it is numeric, A leading 0 is being dropped once the data enters the stream. I can change this to type STRING after it gets into the stream, but the leading 0 is already dropped.

    I have tried to use javascript to evaluate the length of the field and add a leading 0, but cannot get this to work.

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    The types you get are only an indication... you can make them less strict... from number to string always works (the other way around might not).

    Assume you use text file input step (or something similar) change it already there.

    Regards,
    Sven

  3. #3
    Join Date
    Sep 2007
    Posts
    13

    Default More info on first step

    I am using JDBC with the following Select statement to get data. Spoon interprets file as an ODBC connection to a database (flat file database in reality)

    In that first step I use a SQL select statement to get the fields I want. I do not see where/how to change it there. I can only see how Spoon is interpreting the field.

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    Explicitly cast it in your SQL query then.

    Regards,
    Sven

  5. #5
    Join Date
    Sep 2007
    Posts
    13

    Default

    I am decent at this stuff, but not a programmer - example?

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    And I'm not that familiar with AS400... cast/convert the type in your SQL statement. "select cast(field as char(10)) ..." e.g.

    Kettle asks the meta-data of the query to get the datatypes.

    Regards,
    Sven

    P.S you may need to check your SQL manuals how to cast.

  7. #7
    Join Date
    Sep 2007
    Posts
    13

    Default

    After Googling and trial and error, I got this statement to work. Alas it did not solve my problem.

    cast( TRANDATE as varchar(6) )

    The leading 0 is still not getting interpereted, bit the field is coming down as a string/character field.

    I am trying a javacript to lookl at all fields under length of 3 and trying to add a 0, but this is not working. I will start a new thread.

    Thanks for your help.

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.