Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Metadata from Calculator

  1. #1
    Join Date
    Mar 2008
    Posts
    9

    Default Metadata from Calculator

    I've been using the calculator transform to translate NULLs to default values. One of the input values is CHAR(255). I submit this along with a constant to the NVL function and specify String(100) as the output.

    Showing the output fields, is appears correctly.

    However, I get the following error:

    2008/08/25 13:37:06 - DEMO Staging.0 - ERROR (version 3.1.0-RC1, build 771 from 2008/07/08 11:57:00) : Error inserting row into table ["PERSON_DEMO_STG"] with values: [ 1], [ ], [ ], [ ], [ ], [0001/01/01 00:00:00.000], [ ], [ ], [ ], [M], [ ], [ ], [ ], [ ], [ ], [ ], [twurzbac@gmail.com ], [ ], [ ], [ ], [ ], [ ]

    ...

    2008/08/25 13:37:06 - DEMO Staging.0 - ERROR (version 3.1.0-RC1, build 771 from 2008/07/08 11:57:00) : Error setting value #17 [PRI_EMAIL_ADR String(100)] on prepared statement (String)

    Is appears that the type conversion (to String(100)) isn't happening, even though the metadata listing says it is.

    Ideas?

  2. #2
    Join Date
    Mar 2008
    Posts
    9

    Default String conversions/truncations

    After more thrashing about I've determined that the table output step isn't honoring the string lengths set in any step (Select, Calculator, etc). It's reverting back to the original length and erroring with a data truncation error.

    If I use a text file output, it works fine (although that conversion/truncation may be happening directly in the text output step).

    Is it not possible to extend/truncate string length with the Select transform? Or is this a bug?

  3. #3
    Join Date
    Jul 2007
    Posts
    1,013

    Default

    Are you using the "meta-data" tab of the Select/Transform step?

    Be sure not to alter the same field in more than one tab, because they are supposed to be mutually exclusive.

    See http://forums.pentaho.org/showthread.php?t=62687

  4. #4
    Join Date
    Mar 2008
    Posts
    9

    Default Tried both ways

    Hi!

    I've tried using the "Select&Alter" tab (alone), using the metadata tab (with other fields in "Remove" as well as alone) and always the same result.

    If I use a Javascript transform following the Select and create a new field defined as EMAIL_LEN = CEMAIL_NVL.length; I get the original length of 255, not the specified length of 100.

    Chris

  5. #5

    Default

    Altering metadata does not mean altering content.
    If you want to truncate your varchars do it via SQL in your input step or include a Javascript ... len(value) < 100 ? value : substring(...) (or so)

    Christoph

  6. #6
    Join Date
    Jul 2007
    Posts
    1,013

    Default

    Oh, right, I didn't get the string truncation part...

    Christoph's suggestion is correct then. There is also an experimental "TrimCut" plugin that you could check out:

    http://wiki.pentaho.com/display/EAI/...ation+Plug-Ins

    Cheers!

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.