create as select in oracle

Question

I have a little question about creating a table using another tables.

my code:

create table TB_OLAP_TELEFONIA as 

select   ID                AS LOG_ID,
         HORAFIM           AS LOG_HORA,
         DURACAO           AS LOG_DURA,
         TO_CHAR( HORAFIM, 'D')  AS LOG_DIA_SEMANA,
         TO_NUMBER( SUBSTR( NUMEROA, 3, 1 ) ) AS LOG_NUMA_TIPO,
         TO_NUMBER( SUBSTR( NUMEROA, 3, 1 ) ) AS LOG_NUMB_TIPO,
         SUBSTR( NUMEROA, 1, 2) ) AS LOG_NUMA_ESTADO,          
         SUBSTR( NUMEROA, 1, 2) ) AS LOG_NUMB_ESTADO

from     DADOS_COLUNA_OLAP

The sintax are incorrect, and I can't remember what use on the "as" place to give the correct name to column.

Some good glue? Thanks!

Answer 1

Too much ) )

create table TB_OLAP_TELEFONIA as 

select   ID                AS LOG_ID,
         HORAFIM           AS LOG_HORA,
         DURACAO           AS LOG_DURA,
         TO_CHAR( HORAFIM, 'D')  AS LOG_DIA_SEMANA,
         TO_NUMBER( SUBSTR( NUMEROA, 3, 1 ) ) AS LOG_NUMA_TIPO,
         TO_NUMBER( SUBSTR( NUMEROA, 3, 1 ) ) AS LOG_NUMB_TIPO,
         SUBSTR( NUMEROA, 1, 2 ) AS LOG_NUMA_ESTADO,          
         SUBSTR( NUMEROA, 1, 2 ) AS LOG_NUMB_ESTADO

from     DADOS_COLUNA_OLAP

Answer 2

The CTAS syntax is correct. Your problem is a cut'n'paste error. Replace

 SUBSTR( NUMEROA, 1, 2) ) AS LOG_NUMA_ESTADO,
 SUBSTR( NUMEROA, 1, 2) ) AS LOG_NUMB_ESTADO

with

 SUBSTR( NUMEROA, 1, 2)  AS LOG_NUMA_ESTADO, 
 SUBSTR( NUMEROA, 1, 2)  AS LOG_NUMB_ESTADO

edit

While I'm at it I should point out that you are substringing the same column twice, but with different aliases. Did you mean to do this?

 SUBSTR( NUMEROA, 1, 2)  AS LOG_NUMA_ESTADO, 
 SUBSTR( NUMEROB, 1, 2)  AS LOG_NUMB_ESTADO
               ^

If so, you need top fix the column name in both places.