Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Outer Joins

  1. #1
    Join Date
    Jan 2013
    Posts
    18

    Default Outer Joins

    Hi All,

    The joins in Pentaho metadata do not seem to work as I would expect. If someone could point me in the correct direction it would be really helpful.

    To illustrate my actual problem I will give an example -

    Suppose I have two tables "Mug" and and "Drink"

    My "Mug" table looks like -
    Mug Drink
    Red Mug 1
    Yellow Mug

    And my "Drink" table looks like -
    ID Drink
    1 Coffee
    2 Hot Chocolate

    So I define a left outer join (in metadata) from the "Mug" table to the "Drink" table.

    If I then run a query asking for "Mug" from the "Mug" table and "Drink" from the "Drink" table the following SQL is generated -
    Code:
    SELECT
        MUG.Mug,
        DRINK.Drink
    FROM 
        MUG LEFT OUTER JOIN DRINK 
        ON (MUG.Drink = DRINK.ID)
    Which gives me the following result set -
    Mug Drink
    Red Mug Coffee
    Yellow Mug

    Great, that's what I'd expect. However if I then decide to filter the result set so I only see results with the drink "Coffee" I would expect to be running this query -
    Code:
    SELECT
        MUG.Mug,
        DRINK.Drink
    FROM 
        MUG LEFT OUTER JOIN DRINK 
        ON (MUG.Drink = DRINK.ID)
    WHERE
        DRINK.Drink = 'coffee'
    However it's actually running this query -
    Code:
    SELECT
        MUG.Mug,
        DRINK.Drink
    FROM 
        MUG LEFT OUTER JOIN DRINK 
        ON (MUG.Drink = DRINK.ID AND (DRINK.Drink = 'coffee') )
    Which will return all rows from the "Mug" table and only including those from the "DRINK" table where the "Drink" matches "Coffee". With this small dataset it effectively means I get the exact same result set whether or not I apply a filter.

    Is there anything I can do with my model to ensure that any filter the user applies will always get put in the "WHERE" clause and never in the "JOIN ... ON" clause?

  2. #2
    Join Date
    Jan 2013
    Posts
    18

    Default

    After a lot of searching I found the answer at the very end of the Pentaho Metadata Editor User Guide -

    Using the Delay Outer Join Conditions Property
    To force conditions that would ordinarily be processed in the JOIN condition to be processed in the WHERE clause,
    follow the directions below to create a delay_outer_join_conditions custom property.
    1. Right-click on a business model and select Edit.
    2. Add a property by clicking the green + icon.
    3. Select Add a Custom Property and set its ID to delay_outer_join_conditions and select boolean for the Type,
    then click OK.
    4. Select the newly-created delay_outer_join_conditions property, then click the checkbox for
    delay_outer_join_conditions under the Custom heading on the right side of the window, then click OK.
    Instead of the conditions being rolled into the JOIN clause, they will be allowed to roll down into the WHERE clause.

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.