Re: Conditional FROM clause?

From: Someone <abc_at_def.com>
Date: Wed, 29 Sep 2004 13:30:52 -0400
Message-ID: <iK2dnfQZqb9RbMfcRVn-pQ@golden.net>

NetComrade suggested that:
> All,
>
> We have a query that looks something like
>
> select t1.*, t2.*, pkg.func(t1.col1.....)
> from table1 t1, table2 t2
> where t1.id=t2.id
> ....
>
> the query retuns anywhere from 10 to 3000K rows.
>
> The function (pkg.func) does some lookups based on what the value is
> in col1 in other tables (e.g. if col1=1 look at table t3, if col1=2
> look at table t4, etc). So basically we have a query executing for
> each row.
>
> The query is actually fairly complex. We actually have a number of
> similar queries using similar func calls, which consistently show up
> as 'top' queries, and we have been known to be CPU bound in the past.
> I've had a few solutions in mind
>
> 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)
>
> 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?
>
> Thanks

This is where I find ASA's SQL much more valuable than Oracle's. Powerful embedded conditional logic is nicely integrated in their SQL syntax.

That being said, I have found inline views more and more helpful for a complex situation like this lately. You might be able to solve your problem using this technique.

select t1.*, t2.*, decode(t1.col1,1,a.col1,b.col1) as col3 from table1 t1,

      table2 t2,
      (select col1 from t3 where....) a,
      (select col1 from t4 where....) b

where t1.id = t2.id

Not sure if this would work, but maybe you can try something similar.

Phil Received on Wed Sep 29 2004 - 12:30:52 CDT