Conditional Joins

[Former Member]

Can the Universe do conditional join? Can you use conditional statements when editing a join expression? i.e. If (condition; TableA.columnA=TableB.columnA; TableA.columnA=TableB.columnB)

Thanks,

Jimmy

Edited by: Jimmy on May 21, 2009 9:58 PM

[Former Member · Accepted solution]

Hi Jimmy,

You can do complex joins, by editing them in the join window.

for instance:

(
(tableA.columnC="test" AND tableA.columnA=tableB.columnA)
OR 
(tableA.columnC<>"test" AND tableA.columnA=tableB.columnB)
)

You need to set the cadinality first changing this or the selected columns will reset you complex join.

Regards

Alan

[Former Member · Accepted solution]

What I want to do is use an IF or CASE statement in the join expression. Can this be done?

~Jimmy

[Former Member · Accepted solution]

I think it depends on what your underlying database supports.

I noticed a mistake in my previous sample, as this is the same as an If statement

(
(tableA.columnC="test" AND tableA.columnA=tableB.columnA)
OR 
(tableA.columnC != "test" AND tableA.columnA=tableB.columnB)
)

The above code is the same as

if tableA.columnC="test" then
   tableA.columnA=tableB.columnA
else
   tableA.columnA=tableB.columnB

Although I have used tableA in the condition I could use any table from the From clause or even an exists and a subquery.

Regards

Alan

[Former Member · Accepted solution]

Thanks for pointing that out 😃 i didn't see your logic at first.

[Former Member · Accepted solution]

Hi Jimmy,

you could this by writing case statments in the objetcs.

Example: If you have three tables connected and based on condition you can choose the values from different tables.

Case when table1.col = 'X' then table2.measure

else table3.measure

end

Hope this will help you.

Cheers,

Ravichandra