Error when using ALIAS in select

Question

Error when using ALIAS in select

Navigation

#1 by (1 votes)

0

In the select below, I would like to use the novonome alias in the second column (instr).

select 
    substr(titulo, 1, 20) AS **novonome**,
    instr(**novonome**, ' ')+1
from conteudo;

But I get the error:

ORA-00904: "newname": Invalid identifier

sql oracle

asked by anonymous 22.11.2018 / 18:03

1 answer

Initialize a function that has a string pointer as an argument Sub folder configuration in htaccess

score 1 · Answer 1

In reality you have two problems; the first one generates execution error because the alias is defined between two asterisks instead of between quotation marks:

select substr(titulo, 1, 20) AS 'novonome' 
from conteudo;

The second is that you can not use the value returned by the alias as you wish, you would have to play the same function call to return the second column:

select
  substr(titulo, 1, 20) AS 'novonome',
  instr(substr(titulo, 1, 20), ' ') + 1
from conteudo;