Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Apply a filter on a left outer joined table

  1. #1
    Join Date
    Apr 2009
    Posts
    337

    Default Apply a filter on a left outer joined table

    Hi,

    I have left joined table A with table B in my business model. Now, I need to filter on records where table B is empty.

    So, I applied a filter like ISNA(table B. Col A) in my MQL.

    But what this does is, while constructing the SQL, it does:

    table b right join table a on (colx = coly AND table B.col A IS NULL)

    This is not what I want !! Getting the issue? How to solve it ? any help?
    Regards,
    Madhu

  2. #2
    Join Date
    Apr 2009
    Posts
    337

    Default

    back with the answer.
    It is raised in JIRA already! loooong ago!

    http://jira.pentaho.com/browse/PMD-400
    Regards,
    Madhu

  3. #3
    Join Date
    Apr 2009
    Posts
    337

    Default

    The workaround for this is found here:

    http://jira.pentaho.com/browse/BISERVER-3512

    There is the ability to add a property ( delay_outer_join_conditions ) that will force conditions that would ordinarily be rolled into the join clause to be allowed to be processed in the where clause. Here's how you enable the capability:

    1- Modify the business model
    2- Create a new boolean property called delay_outer_join_conditions
    3- Set the value to true (checked)

    Then, instead of the conditions being rolled into the join clause, they'll be allowed to roll down into the WHERE clause.

    Thanks to JIRA and google, as usual! Sadly, again, i had to do it on my own.. nevertheless.. anyone who wants the answer, there you go!
    Last edited by madhupenta; 03-28-2012 at 10:46 AM.
    Regards,
    Madhu

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.