How to use complex join in Metadata Editor ?

[Borowsky]

Hi !

In Metadata Editor I have to join two tables with a join which concern 3 fields.

I must check the complex join checkbox and write the join.

I would like to know which names of table I have to use ? I saw in the wiki of pentaho that :

Also note, the complex join expression provided must utilize the names of the physical tables and physical columns, not business tables and business column names.

What are really the physical name ? names like they are in my database ? Names like they are in Metadata (ID which start with "PT_") ? Names like they are in the business model (ID which start with "BT_") ? Names like they are in business view (ID which start with "BT_") ?

I think I tried all these names but neither of them works...

I have some errors like

Field Name PT_DOSSIER_RMI.BC_DOSSIER_RMI_CODE_DEP not found in Business Model. Business Category PT_DOSSIER_RMI not found.

If you can help me, and tell me how we can create a complex join, I will be very recognizing...

Thanks a lot.

[wgorman]

Hello,

I recently updated that documentation:
http://wiki.pentaho.com/display/Serv...usiness+Tables

The opposite is true now, you want to specify the business tables and business columns using the metadata formula syntax:
http://wiki.pentaho.com/display/Serv...adata+Formulas

Hope that helps!

Will

[Borowsky]

Thanks for you reply.

When I create my join with names of Business Tables (which start with "BT_"), and I try to create a report with one of fields of the table which have a complex join, the page keep on loading... during a lot of minutes, but nothing happen. When I open the pentaho.log, nothing is wrote about that.

I don't know what is the problem...

When I try to create my join with others names, it doesn't work. Indeed when I wanna run my report on the pentaho web, an error occures which tell me that the join is not correct...

Moreover, in Pentaho Metadata Editor (3.0), when we want to create a complex join, we must check the checkbox "Complex Join?" and write the complex join.
But when we do that, we can't validate the join, we have to add a field in the first too lines of the window (From Table/Field and To Table/Field).
It's very strange...

Can you give me the light about that ??

[wgorman]

The fix for the validation issue you are experiencing in 3.0 will be available in our next release. I resolved that a couple weeks ago. What I recommend is using the query editor in the metadata editor to verify the SQL generation. You can also look at the metadata editor log to see any errors.

[Borowsky]

Thank you !

I didn't know the SQL Editor in Metadata. It helps me a lot !

I use the syntax :

AND([BIZ_TABLE_A.BIZ_COL_A]=[BIZ_TABLE_B.BIZ_COL_A];[BIZ_TABLE_A.BIZ_COL_B]=[BIZ_TABLE_B.BIZ_COL_B]).

And it works ! So beautiful !!

Before, I used :

([BIZ_TABLE_A.BIZ_COL_A]=[BIZ_TABLE_B.BIZ_COL_A] AND [BIZ_TABLE_A.BIZ_COL_B]=[BIZ_TABLE_B.BIZ_COL_B]).

It's why it didn't work

Thanks a lot !