Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Conditionally use different table to query against
On Feb 14, 5:08 am, "mwmann" <mwm..._at_gmail.com> wrote:
> Hi anyone/everyone
>
> I have a problem, which I know could be easily done using PL/SQL with
> 2 separate queries, and at run time using IF..THEN to determine which
> of the 2 to use.
>
> However this is not an option and I need to try do this in 1 SQL
> query, for various reasons- There must be a solution (no matter how
> odd/work around it is).
>
> I have 2 tables TRANSACTION_DATA and TRANSACTION_DATA_HISTORY.
>
> My query needs to query TRANSACTION_DATA if inputted numbers of days
> is < 50 ,otherwise use TRANSACTION_DATA_HISTORY.
>
> Any help would be greatly appreciated.
>
> This is pretty URGENT- if anyone can be a Knight In Shining Armour,
> now is your time !!!!!!
Something like this might work:
SELECT
TD.COL1, TD.COL2, TD.COL3, TD.COL4
TDH.COL1, TDH.COL2, TDH.COL3, TDH.COL4
TD.COL1, TD.COL2, TD.COL3, TD.COL4
TDH.COL1, TDH.COL2, TDH.COL3, TDH.COL4
The WHERE clause automatically omits all rows from one SELECT in the UNION ALL. I am not sure if Oracle's optimizer is smart enough to not scan the data in one of the two SELECTs - if you use bind variables (:NUM_DAYS), Oracle will likely have to scan the data in both tables. Testing will tell you whether or not this is the case.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Wed Feb 14 2007 - 04:34:52 CST