Joining three tables with a "single" join?

[Gorka]

Hi all,

I am new to this forum.
My question is the following:
Is there a possibility in Designer to join conditionally three or more tables using a single "conditional join"?
Example:
I have a XXXX table with a column that can have several values (f.e. "A", "B", and "C&quot.
A means transactions in Asia,
B means transactions in Europe,
C means transactions in America.
If the value is A, I want my query to retrieve data from table Asia, if it is B, to retrieve it from table Europe...
Tables A, B and C have the same structure, and the join with the XXXX table is on the same column, f.e. Transaction_code.

Does anyone know if this is possible in B.O.?

Thanks for your help

Gorka

 

[sh4kil]

Try this in the join

(t1.Column1 = 'A' AND t1.column2 = asia.column2) OR (t1.Column1 = 'B' AND t1.column2 = europe.column2) OR (t1.Column1 = 'C' AND t1.column2 = america.column2)

This works in Oracle as I've used it.

 

[skrandel]

Although this will work, it could be very slow.

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com

 

[Gorka]

>Thanks to both of you, I'll try it even if, as skrandel says, looks like it will be quite slow. Probably using table aliases with self-restrictions would be faster.
What I was really looking for was for some kind of B.O. function, that would "redirect" the query to one or another table...

Many Thanks

Gorka

 

[skrandel]

The only thing that does something similar is aggregate aware.

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com

 

[blom0344]

Your remark that the tables are similar would qualify them for creating a single database view on them.

So, instead of three objects there will be only one object in the universe.....

T. Blom
Information analyst
tbl@shimano-eu.com