Complex join syntax

[sebas]

Hi,
I need to use two colums on my relaionship between two tables. So i use the "complex join" option.
But in Metadata editor, when i test the sql query all is Ok but when i publish it it's wrong.
If i put a simple relationship it's ok.

I note that we have to use "physical name" on the complex join.

In MySql :
TA with C1 and C2
TB with C1 and C2

In Metadata :
Physical :
PT_TA with C1 and C2
PT_TB with C1 and C2
Business tables :
BT_TA_TA and BT_TB_TB

I use in the complex join :
"TA.C1=TB.C1 and TA.C2=TB.C2"

Is anybody have a example of complex join ?

Thanks

[sebas]

Sorry it's Ok
When i read "physical name" i use the real name of the table in the Database. And it's the physical name in Metadata.

Bye

[greddin]

I think I'm struggling with this problem now. I created a complex join and tried it with the real table name, then with the metadata-generated Physical Table name and I get this message:

"The column prefix 'PT_VARIABLE_VALUES' does not match with a table name or alias name used in the query."

I'm wondering if the JIRA issue referenced below has somehow broken complex join capabilities. It appears the fix to this issue involved changing the table aliases in the generated SQL. Unless it also changes the names in complex join clauses, those relationships will be broken.

Can anyone comment on that?

http://jira.pentaho.com/browse/PMD-175

[codek]

Sounds feasible, but what version of the platform are you running? That jira is broken in RC1, fixed in GA, but GA isnt out yet is it? So presumably you've built from source or something?

Also; it's a silly thing but you can't use newlines in your complex join syntax. So try removing those if you have any in the editor in metadata..

[greddin]

I'm using bi-server 2.0.0-RC1 and metadata-editor 2.0.0-RC1. I have not tried using a source build yet. Also there are no newlines in the join code.

[greddin]

Ok, I was able to figure this out by debugging the metadata SQL generator at runtime. I put in a breakpoint to figure out what the table aliases are and it uses the "Business Table" id's not the "Physical Table" ids. So, when the post above says "physical name in Metadata" what the poster meant was "Business Table name in metadata".

I do still wonder if the JIRA issue I mentioned could be an issue. On MySQL the table name length is 64 and on MSSql it is 128 so I never encountered a situation where the SQL Generator produced an alias other than the business table name.

It would be very nice if I could turn a debug logger on to see the generated SQL. Is that possible?

[stoddn]

If you do not mind getting into the source, take a look at org.pentaho.platform.web.servlet.AdhocWebService method createJFreeReportDefinitionAsStream wherein you'll find a line that looks like

MQLQuery mql = MQLQueryFactory.getMQLQuery(.....

after which you could log the output of mql.getQuery().getQuery()

It would be nice if this was a standard option, but it might help you for now or at least point you in the right direction.

[DesrochersD]

Hello,

Bumping this because I'm having a hard time removing the errors(more like warns) I get because of complex joins.

The data is filtered as wanted, everything's going well, but everytime I run a report it generates errors such as Failed to parse complex join <...>.

The syntax I am using right now is as said in the previous posts and in this page
Which is : Table1.id = Table2.id AND Table2.CODE = 'String'

Hard-coded, pointing to another table, either way makes it generate the error. I would love to have more information, I've tried quite a few things to fix this but with no luck as of yet.
As I said, It's working right now, but it spams the error log when executing reports.

Thanks in advance!
David