Return Table A or Table B based on condition 2

[ratjetoes]

Hi,

I would like to know what the best approach is to return a table based on a condition.

For example i have a table which has current orders, let's call it ORDERS_CURRENT.

I have another table with the same signature called ORDERS_HISTORY.

Now when i need to retrieve orders i can do this:

SELECT *
FROM ORDERS_CURRENT
WHERE [PARAMETER] = [VALUE_CURRENT]
UNION
SELECT *
FROM ORDERS_HISTORY
WHERE [PARAMETER = [VALUE_HISTORY]

where parameter is the parameter passed to the procedure and VALUE is the value of the parameter which determins if we need current orders or orders from the history table.

What I would like is something like
SELECT *
FROM GetOrderTable([VALUE])

The function then determines based on the [VALUE] which table to return. This makes for much cleaner code plus I don't have to duplicate the query.

What I could do is create a ORDER record type and ORDERS collection type which is returned by the function but I do not know what the best approach is performance wise. Plus it would be nice if I didn't have to create a type because if the number of fields in the table or query changes i have to update the type accordingly.

Anyone any suggestions what the best approach should be?

t.i.a.,
ratjetoes.

 

[Thargy]

ratjetoes,

I believe that the best approach would be to combine those two tables into one, and add a column called IS_HISTORIC which can only be 0 or 1 and must be populated (i.e. has a not null constraint on it).

Your post states that they have the "same signature" which I assume means same structure. Having two tables with identical structure in what I assume to be an OLTP application is usually a mistake.

You would then not need a stored procedure, and could simply say

SELECT *
  FROM ALL_ORDERS
 WHERE IS_HISTORIC = 0;

or for old orders alter the query to have IS_HISTORIC = 1. Having two tables is a bad idea unless there is a business requirement to do so, which is impossible, since the business provides you with requirements, and how they are fulfilled is up to you - right?



Regards

T

 

[ratjetoes]

hi tharg,

unfortunately i can't change the tables, this is just the way the client has setup their database. but i suppose what i could do is create a view with a union on the tables and add a column is_historic.

t.i.a.,
ratjetoes.

 

[Thargy]

rat,

in that case, I suggest you raise a request to have the tables joined. Unless this is a data warehouse situation, it's bordering on criminal de-normalisation.

However, to deal with the issue, try something like:-

CREATE OR REPLACE PROCEDURE fetch_records (HISTORIC IN PLS_INTEGER DEFAULT NULL, or_refcursor OUT sys_refcursor)
  IS
  BEGIN
    IF HISTORIC IS NULL THEN
        OPEN or_refcursor FOR SELECT * FROM ORDERS_CURRENT;
    ELSE
        OPEN or_refcursor FOR SELECT * FROM ORDERS_HISTORIC;
    END IF;            

  END fetch_records;

This is untested, but should give you an idea of how to proceed. Note that this really should be a function, since it returns something, and ideally the in parameter should be a boolean. However, since I have no idea of whether or not this is in a package, or it's being called from sql plus, or some other application, I can't really do much better.

Regards

T

 

[taupirho]

Have a look at the thread below where I give an example of ref cursor usage

http://www.tek-tips.com/viewthread.cfm?qid=1493725

In order to understand recursion, you must first understand recursion.

 

[Dagon]

I think that what ratjetoes is really looking for is a pipeline table function. You would do this is follows:

SQL> create table orders
  2  (order_no   number,
  3  item_no     number,
  4  description varchar2(20))
  5  /

Table created.

SQL> create table order_history
  2  (order_no   number,
  3  item_no     number,
  4  description varchar2(20))
  5  /

Table created.

SQL> insert into orders values (1, 1, 'Some good stuff');

1 row created.

SQL> insert into order_history values (1, 1, 'Some bad stuff');

1 row created.

SQL> create or replace type typ_orders as object
  2  (order_no   number,
  3  item_no     number,
  4  description varchar2(20))
  5  /

Type created.
 
SQL> show errors
No errors.

SQL> create or replace type typ_ord_arr as table of typ_orders
  2  /

Type created.

SQL> show errors
No errors.

SQL> create or replace function ret_orders (p_tab_type in VARCHAR2)
  2    return typ_ord_arr
  3    PIPELINED
  4    as
  5    l_result typ_orders;
  6  begin
  7    if p_tab_type = 'H' then
  8      for r_curr in (select order_no, item_no, description from order_history) loop
  9        l_result  := typ_orders(r_curr.order_no,
 10                                r_curr.item_no,
 11                                r_curr.description);
 12        pipe row(l_result);
 13      end loop;
 14    else
 15      for r_curr in (select order_no, item_no, description from orders) loop
 16        l_result  := typ_orders(r_curr.order_no,
 17                                r_curr.item_no,
 18                                r_curr.description);
 19        pipe row(l_result);
 20      end loop;
 21    end if;
 22    return;
 23  end;
 24  /

Function created.
 
SQL> show errors
No errors.

SQL> select * from table(ret_orders('H'))
  2  /

  ORDER_NO    ITEM_NO DESCRIPTION
---------- ---------- --------------------
         1          1 Some bad stuff

SQL> select * from table(ret_orders('G'))
  2  /

  ORDER_NO    ITEM_NO DESCRIPTION
---------- ---------- --------------------
         1          1 Some good stuff

Retired (not by choice) Oracle contractor.

 

[Thargy]

Nice one Dagon!

I feel a nitwit for not having thought of it myself.

Regards

T

 

[ChrisHunt]

By gum, Dagon, that's useful. I had no idea that any such thing existed.


-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd