Joining 1 table to 2 others

[Former Member]

I have been using the following SQL snippet successfully outside of BOBJ, but I am stumped on how to implement it in Designer:

A left outer join B on
    B.x = A.x
left outer join C on
    C.x = B.x and 
    C.y = B.y
    or
    B.x is null and
    C.x = A.x

Basically, I would prefer to join C to B, but in the case where B.x is null, use table A for joining.

I have implemented the second join in the universe, and it seems to work until execution time when the database complains because BOBJ has placed the second join before the first and of course table B does not exist yet.

Thanks for your suggestions,

Mike

[former_member142702 · Accepted solution]

Have you tried implementing this using a derived table? I assume that this is valid SQL code for your database.

Regards,

Stratos

[Former Member · Accepted solution]

I have not tried this. I have just Googled this and it sounds like a Universe level view? Do you have any suggestions on how to implement this? The only way I can see at the moment would be to create a derived table which is a complete merge of 2 of the tables - in this case table A and B - and then join the third table to the result. I assume the derived table is guaranteed to be created before the other joins are executed. It is the join order that seems to be the root cause of my problem. If I could tell BO to join table A and B first and then join C. I would not have this problem.

I have been using the above join successfully directly with MS-SQL and it has not complained.

Thanks,

Mike

[former_member142702 · Accepted solution]

Please keep in mind that the universe does not generate any tables rather just an SQL statement.

With a derived table you can make a complex SQL statement available as a table for your data model.

Just try to define a derived table based entirely on the SQL statement you have tried succesfully and see if this works.

Regards,

Stratos

PS: What's the version of MS SQL server you are using and what's the exact error message you get?

[Former Member · Accepted solution]

I am using my friend's account since I can't seem to log back into the forums. It doesn't want to accept any of the certificates I have and doesn't seem to want me to log in without one. I don't actually recall allowing it to use any of my certificates in the first place actually.

Stratos,

Thanks for your suggestion, I will give that a try.

BO gives the standard error message:

"A database error occured. The database error text is: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.. (WIS 10901)"

If I send the generated SQL from the SQL Viewer to SQL Server using MS SQL Server Management Studio, I get the following message:

"Msg 4104, Level 16, State 1, Line 126

The multi-part identifier "B.x" could not be bound."

We are using MS SQL Server 2008.

Mike

[former_member142702 · Accepted solution]

First of all you have to find what the correct syntax for your statement to run on MS SQL Server. THis is not really a BO topic.

Regarding the BO error message: Is any of the joined tables itself a view?

Regards,

Stratos