Doing A Calculation on a Prompt

[DataGuru]

I need to union a couple tables together, each table has different data for a term.  Each table is named based on the year and term.

Table naming is the year and then the term example:

table_201503
table_201502
table_201501
table_201403
table_201402
table_201401


Here's the query I was using before to get the data:

Code Select Expand


select *
from table_#prompt('Year and Term','token')#


Then FM would prompt for a year and term, e.g. 201530  and it would select the data from table_201530.

This worked great to pull just 1 year.  But now I need to pull the current year/term and last year for the same term and union them together.

It's simple enough to find last year, just take 201530 - 100 = 201430, however I'm not sure how to do this with one prompt.  I would like to do the calculation on the prompt, but I haven't found a way to do this if it's possible. 

Here's what I've tried:

Code Select Expand


select *
from table_#prompt('Year and Term','token')#
union all
select *
from table_ || to_char(to_number(#prompt('Year and Term','token')#)-100)


I get a syntax error near '||' if I include the pipes, and if I take the pipes out, I get syntax error near 'table_'.

If possible I'd like to only have to do a prompt for one term as we wouldn't need to modify the report.  I know this could be done by creating a second prompt and populate that from the report, but I'd like to be able to do this on the FM side if possible.

Thanks for any help!

[bdbits]

Unless these tables are unacceptably large, I would probably create a view (perhaps materialized) that unioned all the tables together, adding time attributes based on the table the data comes from if the tables themselves have no time attributes. Then you only have to do simple date calculations to check any time period. Otherwise, you will end up building query subjects for every reporting scenario that comes up.