Re: Conditional FROM clause?

netcomradeNSPAM_at_bookexchange.net (NetComrade) wrote in message news:<415adf53.861587066_at_localhost>...

> 1) create an aggregate table that'll always return what the function
> should return
> the drawback is that such table might be very difficult to maintain
> (lots of triggers)

Like Sy, I'd look at MVs. They can help to hide the complexity of the necessary triggers.

>
> 2) for each value in col1 do a separate query and union them in the
> end (col1 #'s are limited)
> i am so far in 'favor' of such solution, but queries aren't going to
> be pretty
>
> 3) do bunch of outer joins.. but the query becomes really ugly, and
> autroace becomes 'bad' and unreadable
>
> What's your advice, and/or other suggestion? Is it possible to have a
> conditional FROM clause in a query based on a column?

2 and 3 effectively reduce to a select in a column query. Like this, using your example:

select t1.*, t2.*,
case t1.col1
when 1 then
(select whatever from wherever)
when 2 then
(select whatever_else from wherever_else) end
from table1 t1, table2 t2
where t1.id=t2.id
....  

and the optimiser does a neat job of
running this, VVF.

You may have to adjust the syntax slightly (I'm doing this in 10g). I know it works with your 8i. But be careful: "case" is not recognised by PL/SQL in that revision. So if you are using this inside PL/SQL, you may have to use
dynamic SQL to workaround. 9i and 10g are OK. Received on Wed Sep 29 2004 - 22:59:43 CDT