PDA

View Full Version : Metadata and 0-1 relationship issue



katsuo
10-29-2007, 05:12 AM
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.CurrencyIdSo 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.CurrencyIdthe 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

MattCasters
10-29-2007, 09:59 AM
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:


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

katsuo
10-30-2007, 04:27 AM
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