Table A that joins to (table b or table c or table d)
A record can exist in a and b but then there is no matching record in tables c and d. I want to create a set of joins in BO designer such that users can query A and get back matching records from each of b,c and d if they are present. Particulalry aggregate counts of records that may exist in any of the child tables.
Any ideas. The PL/SQL behind the application processes these as separate blocks or occasionally using union queries.
Jonathan Bliss, Product Consultant
McKessonHBOC Computer Centre,
Harold Wood Hospital site, Gubbins Lane, Romford, RM3 0NE mobile:0498 668084, tel 01708 336135 (direct line with voicemail), fax 01708 376270
Jonathan,
I think this can be achieved in 2 ways : - One way to do that might be having a denormalized view of b,c and d and then joining A with that view. All the objects pertaining to b,c, and should be defined on this view in that case .
Or, you can join A with b,c and d independently …and since they carry agg . counts as you said ,you can make the objects coming from b incompatible with those of c and d etc …
That way only one of the child tables will be used at a time .what are your opinions …
Thanks,
Vikas
: Joining using Or
Hi All
I have a situation where I have
Table A that joins to (table b or table c or table d)
A record can exist in a and b but then there is no matching record in tables c and d. I want to create a set of joins in BO designer such that users can query A and get back matching records from each of b,c and d if they are present. Particulalry aggregate counts of records that may exist in any of the child tables.
Any ideas. The PL/SQL behind the application processes these as separate blocks or occasionally using union queries.
I think this can be achieved in 2 ways : - One way to do that might be having a denormalized view of b,c and d and
then joining A with that view. All the objects pertaining to b,c, and should
be defined on this view in that case .<
The problem with this is the tables b,c,d have many items that are different, this would require a union join in the view. I’m seriously thinking on these lines but am worried about query performance.
Or, you can join A with b,c and d independently …and since
they carry
agg . counts as you said ,you can make the objects coming from b incompatible with those of c and d etc …
That way only one of the child tables will be used at a time <
This is roughly what I am doing now, but I have an aggregate aware from a table linked to table A that uses stats variously from table b,c or d depending on a unit of volume. this returns no rows as the query tries to join all the tables as if all rows exist in all tables. What I really need is some kind of or join, or a ‘virtual’ null row in each of the two unused tables for each row in the used table.
Jonathan Bliss, Product Consultant
McKessonHBOC Computer Centre,
Harold Wood Hospital site, Gubbins Lane, Romford, RM3 0NE mobile:0498 668084, tel 01708 336135 (direct line with voicemail), fax 01708 376270