Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Null values not being handled in PDI 3.2 SQL Join step

  1. #1
    Join Date
    Dec 2011
    Posts
    9

    Default Null values not being handled in PDI 3.2 SQL Join step

    SELECT COALESCE(
    IF(p.method = 'Acculink', 'Acculink', NULL)
    ,CASE SUBSTR(p.first_six_digits,1,1)
    WHEN 3 THEN 'A'
    WHEN 4 THEN 'V'
    WHEN 5 THEN 'M'
    WHEN 6 THEN 'D'
    END
    ,CAST(pl.method AS CHAR(20))
    ,'Unknown')
    AS payment_method
    FROM payment_ p
    WHERE p.id = ?

    The whole point is to check to see if the p.method is Acculink, if it is, print that. Otherwise check some other field and based on it, print certain things. Otherwise print a raw field, or else print "Unknown"

    Pretty simple. This query works flawlessly when run against a database raw, but when this is put into PDI sql join step, it results in payment_method errors. The DB doesnt allow nulls in payment_method, this function is supposed to write "Unknown" if there isn't one (this is set up as an outer join) but it just throws errors saying there are nulls resulting from this anyways.

    Can anyone shed some light on this? Thanks.

  2. #2
    Join Date
    Dec 2011
    Posts
    9

    Default

    I've still got this problem, I've used a workaround for the last year. I use the null to values step, and just set nulls to a number that won't be in the dataset, and just test for that value. I'd rather not do that, I hope there is more information on this subject.

  3. #3
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    You should report this to devs:
    http://jira.pentaho.com/secure/Dashboard.jspa

    Remember to specify which version of PDI you're using, version of DB, how you connect to database and which driver you use.

    Mick.

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.