Re: how to retrieve fields & tables with name ending with YYYYMM

lai (lyekheng_at_hotmail.com) wrote:
: Hi,

: I have fields and tables with name ending with YYYYMM, e.g.
: gross_amount_YYYYMM field, trfc_YYYY_YYYY table. If the current year is
: 1999 and month is March, then the data would extract gross_amount_199903
: field from trfc_1998_1999 table. Is there a way to do that?

...an example...

create or replace view ALL_TRFC as
select gross_amount_199903 gross_amount,

       '199903' yyyymm
from trfc_1998_1999
union all
select gross_amount_199904 gross_amount,

       '199904' yyyymm
from trfc_1998_1999
union all
select gross_amount_199905 gross_amount,

       '199905' yyyymm
from trfc_1998_1999
;

now we can use SQL such as

  select * from ALL_TRFC where yyyymm between '199902' and '199903';

Each year add the new year's table into the view definition and rebuild the view.

You may wish to build some indexes on the trfc_yyyy_yyyy tables. Received on Fri Dec 17 1999 - 16:31:52 CST