Thread: sql and timestamp variable

Howdy:

I have a query where I hardcode (and manually change)
a table name that has the date as part of it's name.

[snip examples]

select count(*) from  db2_ps_regprof_200304 where mbr is null;

--

update db2_ps_fac_up200304
set mbr=mbr_num
from t_pos_mbr
where
contract_num = contract and
sex_code = sex and
birth_year_num = byr and
birth_mt_num = bmm and
birth_day_num=bday and
substr(patient_first_name,1,1) = substr(mbrfirst,1,1)

[/snip examples]

The tables will change every month, so, where this month
the table name is <blah-blah-blah_200304> , will be
<blah-blah-blah_200305> next month. 

I don't want to totally change the process; I want to know if
it's possible to create some variable and have it
attached to the name of the tables so it will know
exactly which one to modify.

Something like

[snip wishful thinking]

select count(*) from  db2_ps_regprof_$datemonth where mbr is null ;

[/snip wishful thinking]

How can you do this?  Otherwise, I may have to do this in Perl,
perhaps?

Thanks!

-X

On Thursday 15 May 2003 3:54 pm, Johnson, Shaunn wrote:
> Howdy:
>
> I have a query where I hardcode (and manually change)
> a table name that has the date as part of it's name.
>
> [snip examples]
>
> select count(*) from  db2_ps_regprof_200304 where mbr is null;

I'm not sure if this might help:

1. Create a view "db2_ps_regprof_current"
2. Make sure all your access is through that view
3. Recreate the view at every month-end (via a cron-job).

Of course, that's only useful for the specific case of "this month" and might
be fiddly if the database is in use 24/7
--
  Richard Huxton

> select count(*) from  db2_ps_regprof_$datemonth where mbr is null ;
>
> [/snip wishful thinking]
>
> How can you do this?  Otherwise, I may have to do this in Perl,
> perhaps?

Try this in psql:

\set datemonth xxxxxxx  (fill in whatever you need here)

select count(*) from db2_ps_regprof_:datemonth where mbr is null;

Is that what you're after?

You could even pre-load some values via the .psqlrc file (and update
that file with a cron job as needed) to pre-load some values:

select count(*) from db2_ps_regprof_:current where mbr is null;
select count(*) from db2_ps_regprof_:last where mbr is null;
etc.

There's some stuff in the psql documentation about % and backticks, but
that appears to only work with prompts.  What would be nice would be if the
text in backticks could also be executed and the results fed into a
set command, so that you could do the following:

\set datemonth `select table_name from table_select where month = 'Jan';`
--
Mike Nolan