Dynamic From Clause To Create View

[ivester]

Hi,

I'm after some help with a view I am trying to create, but Im not sure if I am going about it correctly

I have hundreds of log tables, can be any number created within a month. I need to identify the last 3 months worth and union them together into one view.

I can get the log table created date/time from syscat tables which helps me identify which tables I need look at but I am unsure how to incorpoate that in my select... ie

create view ??? as select ??? from (select tabname from syscat.tables where tbname = ?? and create_time between current_timestamp - 3 MONTH and current_timestamp)

Is it possible to do something like this ?

Thanks

[sushanth bobby]

Ivester,

select ??? from (select tabname from syscat.tables where tbname = ?? and create_time between current_timestamp - 3 MONTH and current_timestamp)

I have tried something like this, result was a flop.
It might work in a higher DB2 version like DB2 V9. So far i did'nt get a chance to use V9.

Since you have hundereds of tables created & the tables can be got from DB2 catalog.

select 'select ??? from ',strip(tabname), 'union all'
from syscat.tables 
where tbname = ?? and create_time between current_timestamp - 3 MONTH and current_timestamp

This will get you all the necessary selects for creating a view. After this you need to do a little editing in top add[create view vname(???)] and in the bottom remove the extra union all

Sushanth Bobby

[ivester]

Thanks for the reply Sushanth Bobby - that is the route I have started going down. With a bit of tweaking it should get me just what i'm looking for!