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

Question

I want to have a single parameter in my script for a year.

define YYYY = 2014;

then I want a 2nd parameter based on this one

YYYY2 = &&YYYY - 1;

i.e. it's 1 year earlier

But from the reading i've done so far this seems to be impossible.

Here's my attempt

define YYYY = 2014;
define YYYY2 = &&YYYY - 1;

select &&YYYY, &&YYYY2 from DUAL

select * from cb_enrolment2_&&YYYY;
select * from cb_enrolment2_&&YYYY2 ;

response for last line of code is :

ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause: 
*Action: 
Error at Line: x Column: y

Answer 1

That happens because you assign, literally, 2014 - 1 to YYYY2.

So:

select &&YYYY, &&YYYY2 from DUAL

turns into

select 2014, 2014 - 1 from DUAL

which is a valid query, but

select * from cb_enrolment2_&&YYYY2

turns into

select * from cb_enrolment2_2014 - 1

which gives you an error.

You can't do math directly on substitution variables, but you can select data from database into them, using column command with new_value argument. So, nothing stops you from selecting an arithmetical expression from dual:

define YYYY = 2014;
column dual_x new_value YYYY2;
select &&YYYY - 1 dual_x from dual;

YYYY2 is created implicitly while executing column and select, so there's no need to define it.

SQLDeveloper uses dialect of SQL*Plus in its worksheet, see this for reference and more details on substitution variables: https://blogs.oracle.com/opal/entry/sqlplus_101_substitution_varia#2_5