Re: Conditionally use different table to query against

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 14 Feb 2007 02:34:52 -0800
Message-ID: <1171449292.684000.294580@q2g2000cwa.googlegroups.com>

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

FROM
  TRANSACTION_DATA TD
WHERE
  :NUM_DAYS < 50
UNION ALL
SELECT

  TDH.COL1,
  TDH.COL2,
  TDH.COL3,
  TDH.COL4

FROM
  TRANSACTION_DATA_HISTORY TDH
WHERE
  :NUM_DAYS >= 50; Where :NUM_DAYS is the user input for the number of days. For example, if the user enters 100, the SQL statement would be evaluated like this:
SELECT

  TD.COL1,
  TD.COL2,
  TD.COL3,
  TD.COL4

FROM
  TRANSACTION_DATA TD
WHERE
  100 < 50
UNION ALL
SELECT

  TDH.COL1,
  TDH.COL2,
  TDH.COL3,
  TDH.COL4

FROM
  TRANSACTION_DATA_HISTORY TDH
WHERE
  100 >= 50;

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