I created a table through a pivot statement, which automatically creates some variable names that start with a number.
create table MYTAB as
select *
from (select x, anno, v, delta from tab_a13_2 where anno in(2017,2018,2019))
pivot(sum(v) as v, sum(delta) as d for anno in (2017,2018,2019))
where ordine > 0
order by ordine;
select * from MYTAB;
x 2017_V 2017_D 2018_V 2018_D 2019_V 2019_D
1 1.01 -3.18 1.04 11.18 0.96 -6.87
2 1.28 0.09 1.28 7.33 1.25 -1.49
...
However, if I try to specify the column names in a select, I get this error:
select x,
2017_V, 2018_V, 2019_V,
2017_D, 2018_D, 2019_D
from MYTAB;
Error at line 5:
ORA-00911: invalid character
2017_V, 2018_V, 2019_V,
^
1 statement failed.
I don't get it. Either I am not allowed to create column names that start with a number, and therefore the table creation should fail, or I should be able to use them. I checked that the column names are not quoted, i.e. '2017_V'.
From the Database Object Names and Qualifiers documentation:
Database Object Naming Rules
Every database object has a name. In a SQL statement, you represent the name of an object with a quoted identifier or a nonquoted identifier.
- A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.
- A nonquoted identifier is not surrounded by any punctuation.
...
- Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.
You have an identifier that begins with a number. This tells you that the solution is to use a quoted identifier and surround the column name with double quotes:
select x,
"2017_V",
"2018_V",
"2019_V",
"2017_D",
"2018_D",
"2019_D"
from MYTAB;