Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Problem with if...else construct

  1. #1
    Join Date
    Oct 2012
    Posts
    10

    Question Problem with if...else construct

    I am having a problem with an if...else construct that works correctly when in a test transformation, but does not return the same values when in my actual transformation. Here is the logic.

    Name:  Simple_Trans.jpg
Views: 61
Size:  9.3 KB

    NOTES is a varchar which is read from database_1 table Audio and it has some fields that are null. If the NOTES is empty I want to assign nothing or null to FLDNARRATIVE. If NOTES has something in it (a string), I want to set both FLDNARRTIVE and FLDTYPE equal to 1.
    FLDNARRATIVE is an integer and is written to database_2 table HEAR
    FLDTYPE is an integer and is written to database_2 table HEAR_MEMO

    if (isEmpty(NOTES)) {
    FLDNARRTIVE.setValue('');
    } else {
    FLDNARRTIVE.setValue(1);
    FLDTYPE.setValue(1);
    }

    I use the Java Filter to direct the rows to the proper database with the following conditional (FLDNARRTIVE.equals(1)). In my test transformation this produces the results that I am looking for. Unfortunately when I place this logic into my real transformation, the value FLDNARRATIVE is always 1.

    Name:  Output.PNG
Views: 59
Size:  9.3 KB

    Can anyone see any reason that this logic would not work in my real transformation?

    Thanks for you help in advance.

    Spoon 4.4.0 Running on Windows 7 Pro 64bit, 2008 MSSQL Database - Java 1.7.0_21

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

    Default

    Quote Originally Posted by Munimula View Post

    if (isEmpty(NOTES)) {
    FLDNARRTIVE.setValue('');
    } else {
    FLDNARRTIVE.setValue(1);
    FLDTYPE.setValue(1);
    }
    This is outdated notation, and shouldn't be used in the Modified Java Script Value step.

    Instead, use:

    Code:
    var VLDNARRITIVE
    var FLDTYPE
    
    if (isEmpty(NOTES)) {
        FLDNARRTIVE='';
    } else {
        FLDNARRTIVE=1;
        FLDTYPE=1;
    }
    And include the variables in the box below the code box.

    Also - why use the Java Filter, rather than the Filter Rows step?
    With Filter rows, you can use FLDNARRTIVE = 1...

    Next question... Since I'm going to assume that you are comparing data from your Data Grid as your Test, and your DB as your "real" transformation... Are you sure that isEmpty(NOTES) will truly reflect what your DB is sending? Could your DB be sending ' ' instead? Does your database differentiate between '' and NULL?
    Last edited by gutlez; 06-24-2013 at 07:41 PM.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  3. #3
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    I'm pretty sure the database is null-aware.
    So, if I would use JavaScript at all, my code would look like this:

    Code:
    var FLDNARRATIVE = NOTES == null ? null : 1;
    var FLDTYPE = FLDNARRATIVE;
    Action "Get variables" will add two new fields to the stream, so no need to Add Constants beforehand - just in case ...

    Caveat: The code above does always initialize FLDTYPE.
    So long, and thanks for all the fish.

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

    Default

    Yes, the DB is null aware, but we've all seen creative programmers who do strange things like store ' ' instead of null...
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  5. #5
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    That's true, gutlez. I even can see what triggered this thought: setValue('')

    I'm confident, though, the OP will learn about the pitfalls of inconsistent null handling during the process.
    So long, and thanks for all the fish.

  6. #6
    Join Date
    Oct 2012
    Posts
    10

    Default

    Thank you for your help, it certainly helped me understand why I was having so many problems with JavaScript. I quit using Compatibility Mode, and now writing JavaScript is much easier and understandable. Here is the final if...else statement that I come up with, it handles everything correctly down to inserting null into the FLDNARRTIVE field if NOTES is not true. I ended up having to rewrite all of the JavaScript's in the Transformation so they could run without Compatibility Mode but it was worth the time and effort.

    var FLDNARRTIVE;
    var FLDTYPE;

    if (NOTES) {
    FLDNARRTIVE=1;
    FLDTYPE=1;
    } else {
    FLDNARRTIVE=null;
    }

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.