0

Hi I was copying a employees table in HR schema with query:

Create table copy_employees as ( select last_name,salary*12 from employees);

But the error shows that salary needs to be aliased.

After I did salary*12 as sal it worked.

My question is why is this aliasing needed?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Atul Singh
  • 9
  • 2

1 Answers1

4

a CTAS ( Create Table As Select ) statement is just like a Create Table DDL statement in which column naming has some conventions, such as using

  • the underscore "_"

  • the dollar sign "$"

  • pound sign "#"

    in a column name is allowed, but asterisk "*" ( like in your case ) is not allowed to use, so it's impossible to create a table containing a column with an asterisk. In this case, Oracle tries to use the expression salary*12 as the column name and fails.

Community
  • 1
  • 1
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • 1
    Spot on - might be worth noting explicitly that by default, Oracle uses the expression itself as the column name if no alias is given (in this case, it tries to use `salary*12`). – Frank Schmitt Jun 02 '19 at 11:36
  • @frank it tries to use salary*12 and fails.. Aliasing is therefore mandatory. But why? – Atul Singh Jun 02 '19 at 14:09
  • How exactly would you call that column, @Atul? Did you ever try to `create table test (salary*12 number);` ? `salary * 12` is an expression; give it a name. Why? Because that's how it goes. – Littlefoot Jun 02 '19 at 16:14