Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Metadata and 0-1 relationship issue

  1. #1
    Join Date
    Oct 2007
    Posts
    5

    Default Metadata and 0-1 relationship issue

    Hi everybody,

    I have started using the Metadata layer today, and it looks like a great tool !

    But I have for the moment two issues which prevents me to do some reports.

    The first one is due to the following relationship

    Company Table
    CompanyId int (PK)
    CompanyName varchar
    CurrencyId int

    Currency Table
    CurrencyId int (PK)
    CurrencyName varchar

    CurrencyId can be null in the company table.
    Company may have no currency.


    The issue is that whatever the type of relationship I choose into the Metadata editor for setting 'relationship properties',
    it generates the following sql

    SELECT DISTINCT
    Currency.CurrencyName AS COL0
    ,Company.CompanyName AS COL1
    FROM
    currency Currency
    ,company Company
    WHERE
    Company.CalculatedMarketValueCurrencyId = Currency.CurrencyId
    So only the company row with a CurrencyId specified are included in the report.

    I have tried to "play" with the "complex join?" feature but only the where clause can be modified

    According to me, rather than

    FROM
    currency Currency
    ,company Company
    WHERE
    Company.CalculatedMarketValueCurrencyId = Currency.CurrencyId
    the following SQL should have been generated

    FROM
    company Company LEFT JOIN currency Currency
    ON Company.CalculatedMarketValueCurrencyId = Currency.CurrencyId
    Is there any workaround to this issue ?

    Is there any concept I have missed which should help me to solve this scenario ?


    I have the same issue with the following relationship :

    Company Table
    CompanyId int (PK)
    CompanyName varchar

    Market Table
    MarketId int (PK)
    CompanyId int

    In this scenarion, if a company has not any market, it is not part of the report, but in my business case, a Company can have 0 to N markets linked to it.


    Thanks in advance for your help !

    Best regards,
    Christophe

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Actually, I don't think we have support for outer joins in there.
    Personally I would prefer it very much if support for it would never end up in PMD either.
    However, realistically I suppose someone will add it sooner or later. :-)

    It depends on the database of-course, but on databases like Oracle you can write it like this:

    Code:
    SELECT DISTINCT 
          Currency.CurrencyName AS COL0
         ,Company.CompanyName AS COL1
    FROM 
                   currency Currency
                 ,company Company
    WHERE 
                   Company.CalculatedMarketValueCurrencyId = Currency.CurrencyId (+)
    Then again, the real problem is that you need the outer join at all. It will keep causing problems for you until you build a real data warehouse.

    HTH,

    Matt

  3. #3
    Join Date
    Oct 2007
    Posts
    5

    Default

    Thanks for the reply Matt.

    Would it be possible to use views rather than table in Metadata editor ?

    The views don't appear into the "import Tables..." menu item on a connection. Is there another way to do it please ?

    Best regards,
    Christophe

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.