Search code examples
oracle-sqldeveloper

select a column name with a variable

i have a table with 10 columns ,with names: cost201907, cost201906, cost201905 ......

I want to put in a variable the today month and year: a = 201907

define a = to_char(SYSDATE, 'yyyy')|| to_char(SYSDATE, 'mm') from dual;

SELECT &a;

output: a = 201907

My goal is to select a column using this variable.

Each month run a script that give me today's date in a variable,

and after use it to select the most recent columns with a select statement, such as: 

Select  'cost'||a  from Table1.

Probably i ll use PL/SQL but still cant find a way. Please help me.

Solution

  • You can use a substitution variable, but you need to populate it slightly differently, form the column name properly.

    At the moment you're using define, which gives a the value of the entire string you set; then your select gets that whole string as a replacement, which you can see with set verify on:

    SELECT &a;

old:SELECT &a
new:SELECT to_char(SYSDATE, 'yyyy')|| to_char(SYSDATE, 'mm') from dual

TO_CHA
------
201907

    Then when you do

    Select  'cost'a  from Table1;

    you are selecting the text literal 'cost', and giving that column expression the alias a - completely unrelated to your variable; so you'll see output like:

    A   
----
cost
cost
cost
...

    with one output row for every row in your table.

    Instead of your define, use the column ... new_value ... option to make the result of a dummy query available as a substitution variable for a later one:

    column a new_value a;

select to_char(sysdate, 'YYYYMM') as a from dual;

A     
------
201907

select  cost&a  from table1;

old:select  cost&a  from table1
new:select  cost201907  from table1

COST201907
----------
        42
...

    You can wrap the dummy query in a set termout off and ... on pair, though you still seem to need to run your script from a file with @ to make that take effect properly. And you can set verify off to hide the old/new display once you're happy with it.