Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: SQLite dynamic number typing causing issues

  1. #1
    Join Date
    May 2010
    Posts
    11

    Default SQLite dynamic number typing causing issues

    Hey Folks,

    I've been using kettle for a long time now, but I recently came up against an issue where the type of the first row was affecting the table input step:
    In SQLite, I have a column Typed as 'Numeric' which can store integers as integers, but floats as floats, etc.
    In my table, I have a column with both integer and float (real) values.

    When I use the table input step, and a integer value is in the first row, all rows are typed as integer, and I cannot retrieve the float values (they are rounded, and not just masked!). I've tried using a Select Values, Alter metadata step, but the value has definitely been lost.

    If I order my table input query to return the float value first, then it seems that everything is considered a float and works fine.

    At this point I can change the structure of my column, from 'Numeric' (which is what Kettle originally suggests) to 'Real', which forces integers to be typed as real, but I imagine that this could cause issues for others.

    Has anyone else ever come across this? Should I raise this as a bug request ( I can't find any other references from a solid googling)?

    PDI: V7.1.0.0-12, Windows.
    Thanks for your help!

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

    Default

    Try casting your numeric as real.

    Table Input:
    Select CAST(field AS REAL) from Table

    https://www.sqlite.org/lang_expr.html

  3. #3
    Join Date
    May 2010
    Posts
    11

    Default

    Perfect. That's an even easier solution!

Tags for this Thread

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.