oracle - SQL Developer, substitution variable, dynamic creation, based on equation -

i want have single parameter in script year.

define yyyy = 2014; 

then want 2nd parameter based on one

yyyy2 = &&yyyy - 1; 

i.e. it's 1 year earlier

but reading i've done far seems impossible.

here's attempt

define yyyy = 2014; define yyyy2 = &&yyyy - 1;  select &&yyyy, &&yyyy2 dual  select * cb_enrolment2_&&yyyy; select * cb_enrolment2_&&yyyy2 ; 

response last line of code :

ora-00933: sql command not ended 00933. 00000 - "sql command not ended" *cause:  *action:  error @ line: x column: y 

that happens because assign, literally, 2014 - 1 yyyy2.

so:

select &&yyyy, &&yyyy2 dual 

turns

select 2014, 2014 - 1 dual 

which valid query, but

select * cb_enrolment2_&&yyyy2 

turns into

select * cb_enrolment2_2014 - 1 

which gives error.

you can't math directly on substitution variables, can select data database them, using column command new_value argument. so, nothing stops selecting arithmetical expression dual:

define yyyy = 2014; column dual_x new_value yyyy2; select &&yyyy - 1 dual_x dual; 

yyyy2 created implicitly while executing column , select, there's no need define it.

sqldeveloper uses dialect of sql*plus in worksheet, see reference , more details on substitution variables: https://blogs.oracle.com/opal/entry/sqlplus_101_substitution_varia#2_5