Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: converting NULL to the Empty String....

  1. #1

    Default converting NULL to the Empty String....

    I'm running 3.2.5 and DB2/Z 9.1.5. I have a simple job that reads a pipe-delimited file with data like name and address and writes it out to table. Some of the incoming fields will have no data and I'd like to write them out as the empty string, but they are written out as NULL using Table Output. The "If Null" step doesn't seem to have a way to convert NULL to the empty string.

    I've seen the info in PDI-1796 and PDI-2277
    http://jira.pentaho.com/browse/PDI-1796
    http://jira.pentaho.com/browse/PDI-2277

    I tried installing 4.0.1 as a test and setting the following line in my Kettle properties file:
    KETTLE_EMPTY_STRING_DIFFERS_FROM_NULL = Y

    When I run my transformation I still get NULL output fields. I'm obviously missing something here. Would someone please give me a hint?

    Thanks!

    Dave

  2. #2

    Default

    I've tried a few more things, but nothing has worked so far to get an empty string in my table output.


    1. tried using IF NULL but you can't set a value of "" or '' (empty double/single quotes)
    2. value mapper doesn't allow an empty string as target
    3. calling Javascript with if (ADDR2 == null) {ADDR2 = "";} had no effect
    4. linking to a final SQL SCRIPT step that did an "update mytable set addr2=coalesce(addr,'');" didn't work as it ran first even though I had it run after a final Blocking Step.

    I guess the Table Output is automatically converting empty strings to NULL.

    I'm coming from a DataStage/QualityStage environment and trying to convert some existing jobs over to Kettle. In DS I could specify the empty string to be used for NULL values and I could also create a custom INSERT statement where I do the COALESCE as in "INSERT INTO MYTABLE (FIELD1, FIELD2, FIELD3) VALUES (?, ?, COALESCE(?,'') );"

    I do not see either of those options available to me in Kettle. There's always a steep initial learning curve for new products. I'm hoping I'm just missing seeing the correct option somewhere.

    Dave

  3. #3
    Join Date
    Jun 2010
    Posts
    114

    Default

    Yep. I've tried assigning empty string in modified JS and tried storing it in table output and yeah, it is stored as NULL
    Gotta see how it can be resolved
    Thanks,
    Om
    ----------------------
    Spoon version -4.4.0

  4. #4

    Default

    I downloaded the 4.0.1CE version and tried setting KETTLE_EMPTY_STRING_DIFFERS_FROM_NULL=Y in my Kettle properties file, but I still get NULL data instead of the empty string in Table Output. I verified the property string is there and equal to "Y", I can see it when I do a ctrl-space in a Get Variable step.

    Is this the only thing I should have to do get this to work? Am I missing a step?

    Thanks for your help.

    Dave

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.