asked on

ORA-00904: string: invalid identifier

Hi, i´m converting this Firebird view to Oracle and I´m getting this error: ORA-00904: string: invalid identifier. I cant see why this is happening, since the table name is correct, the field exists on the respective table. I´m newbie to Oracle and need help. Any ideas?

PS: I'm posting the view and the related tables.

CREATE VIEW FB_ORACLE.VIEW_CTBCLIENTECONTABIL(
  REFERENCIA,
  FK_PLANOCONTA,
  DESCRICAO,
  RAZAOSOCIALCONTABIL,
  DATAINICIO,
  DATATERMINO,
  FK_EXERCICIOSTATUS,
  IE,
  CEP,
  ENDERECO,
  NUMERO,
  COMPLEMENTO,
  BAIRRO,
  MUNICIPIO,
  UF,
  CODIGOPERFIL,
  DATAABERTURA,
  NUMEROREGISTRO,
  NOMEORGAOREGISTRO,
  CNPJ,
  CEI,
  CPF,
  NOMERESPONSAVEL,
  CPFRESPONSAVEL,
  RGRESPONSAVEL,
  CARGORESPONSAVEL,
  NOMECONTADOR,
  CRCCONTADOR,
  UFCRCCONTADOR)
AS
SELECT EXE.REFERENCIA,
       EXE.FK_PLANOCONTA,
       EXE.DESCRICAO,
       EXE.RAZAOSOCIALCONTABIL,
       EXE.DATAINICIO,
       EXE.DATATERMINO,
       EXE.FK_EXERCICIOSTATUS,
       EXE.IE,
       EXE.CEP,
       EXE.ENDERECO,
       EXE.NUMERO,
       EXE.COMPLEMENTO,
       EXE.BAIRRO,
       MUN.MUNICIPIO,
       EXE.FK_UF,
       PER.PK_PERFIL,
       PER.DATAABERTURA,
       PER.NUMEROREGISTRO,
       PER.NOMEORGAOREGISTRO,
       PER.CNPJ,
       PER.CEI,
       PER.CPF,
       (SELECT RES.NOME
          FROM FB_ORACLE.CTBRESPONSAVEL RES
               INNER JOIN
               FB_ORACLE.CTBRESPONSAVELEXERCICIO REX ON (EXE.PK_EXERCICIO = REX.FK_EXERCICIO)
         WHERE REX.FK_RESPONSAVEL = RES.PK_RESPONSAVEL
           AND RES.SOCIO = '1'
           AND RES.ATIVO = '1' AND ROWNUM = 1) AS "NOMERESPONSAVEL",
 
       (SELECT RES.CPF
          FROM FB_ORACLE.CTBRESPONSAVEL RES
               INNER JOIN
               FB_ORACLE.CTBRESPONSAVELEXERCICIO REX ON (EXE.PK_EXERCICIO = REX.FK_EXERCICIO)
         WHERE REX.FK_RESPONSAVEL = RES.PK_RESPONSAVEL
           AND RES.SOCIO = '1'
           AND RES.ATIVO = '1' AND ROWNUM = 1) AS "CPFRESPONSAVEL",
 
       (SELECT RES.RG
          FROM FB_ORACLE.CTBRESPONSAVEL RES
               INNER JOIN
               FB_ORACLE.CTBRESPONSAVELEXERCICIO REX ON (EXE.PK_EXERCICIO = REX.FK_EXERCICIO)
         WHERE REX.FK_RESPONSAVEL = RES.PK_RESPONSAVEL
           AND RES.SOCIO = '1'
           AND RES.ATIVO = '1' AND ROWNUM = 1) AS "RGRESPONSAVEL",
 
       (SELECT RES.CARGO
          FROM FB_ORACLE.CTBRESPONSAVEL RES
               INNER JOIN
               FB_ORACLE.CTBRESPONSAVELEXERCICIO REX ON (EXE.PK_EXERCICIO = REX.FK_EXERCICIO)
         WHERE REX.FK_RESPONSAVEL = RES.PK_RESPONSAVEL
           AND RES.SOCIO = '1'
           AND RES.ATIVO = '1' AND ROWNUM = 1) AS "CARGORESPONSAVEL",
 
       (SELECT CON.NOME
          FROM FB_ORACLE.CTBRESPONSAVEL CON
               INNER JOIN
               FB_ORACLE.CTBRESPONSAVELEXERCICIO REX ON (EXE.PK_EXERCICIO = REX.FK_EXERCICIO)
         WHERE REX.FK_RESPONSAVEL = CON.PK_RESPONSAVEL
           AND CON.CONTADOR = '1'
           AND CON.ATIVO = '1' AND ROWNUM = 1) AS "NOMECONTADOR",
 
       (SELECT CON.CRC
          FROM FB_ORACLE.CTBRESPONSAVEL CON
               INNER JOIN
               FB_ORACLE.CTBRESPONSAVELEXERCICIO REX ON (EXE.PK_EXERCICIO = REX.FK_EXERCICIO)
         WHERE REX.FK_RESPONSAVEL = CON.PK_RESPONSAVEL
           AND CON.CONTADOR = '1'
           AND CON.ATIVO = '1' AND ROWNUM = 1) AS "CRCCONTADOR",
 
       (SELECT CON.FK_CRCUF
          FROM FB_ORACLE.CTBRESPONSAVEL CON
               INNER JOIN
               FB_ORACLE.CTBRESPONSAVELEXERCICIO REX ON (EXE.PK_EXERCICIO = REX.FK_EXERCICIO)
         WHERE REX.FK_RESPONSAVEL = CON.PK_RESPONSAVEL
           AND CON.CONTADOR = '1'
           AND CON.ATIVO = '1' AND ROWNUM = 1) AS "UFCRCCONTADOR"
  FROM FB_ORACLE.CTBEXERCICIO EXE
       INNER JOIN
       FB_ORACLE.CTBPERFIL PER ON (EXE.FK_PERFIL = PER.PK_PERFIL)
       INNER JOIN
       FB_ORACLE.ALLMUNICIPIO MUN ON (EXE.FK_MUNICIPIO = MUN.PK_MUNICIPIO);
 
 
 
 
CREATE TABLE FB_ORACLE.CTBEXERCICIO
(
  PK_EXERCICIO NUMBER,
  FK_PLANOCONTA NUMBER NOT NULL,
  FK_PERFIL NUMBER NOT NULL,
  DESCRICAO VARCHAR2(60),
  DATAINICIO DATE NOT NULL,
  DATATERMINO DATE NOT NULL,
  RAZAOSOCIALCONTABIL VARCHAR2(60),
  IE VARCHAR2(18),
  ENDERECO VARCHAR2(60),
  NUMERO CHAR(5),
  COMPLEMENTO VARCHAR2(20),
  BAIRRO VARCHAR2(60),
  FK_MUNICIPIO NUMBER NOT NULL,
  CEP CHAR(9),
  REFERENCIA CHAR(4),
  FK_UF CHAR(2),
  FK_EXERCICIOSTATUS NUMBER NOT NULL,
  DDD NUMBER DEFAULT 0,
  TELEFONE VARCHAR2(15) DEFAULT '',
  CONSTRAINT PCTBEXERCICIO
  PRIMARY KEY (
    PK_EXERCICIO
  )
  USING INDEX
  PCTFREE 10
  INITRANS 2
  STORAGE (
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
  )
  TABLESPACE USERS ENABLE VALIDATE
)
NOPARALLEL
PCTFREE 10
INITRANS 1
STORAGE (
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
TABLESPACE USERS
LOGGING
NOCACHE
MONITORING
NOROWDEPENDENCIES;
 
 
 
 
 
CREATE TABLE FB_ORACLE.CTBRESPONSAVELEXERCICIO
(
  PK_RESPONSAVELEXERCICIO NUMBER,
  FK_RESPONSAVEL NUMBER NOT NULL,
  FK_EXERCICIO NUMBER NOT NULL,
  CONSTRAINT PCTBRESPONSAVELEXERCICIO
  PRIMARY KEY (
    PK_RESPONSAVELEXERCICIO
  )
  USING INDEX
  PCTFREE 10
  INITRANS 2
  STORAGE (
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
  )
  TABLESPACE USERS ENABLE VALIDATE
)
NOPARALLEL
PCTFREE 10
INITRANS 1
STORAGE (
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
TABLESPACE USERS
LOGGING
NOCACHE
MONITORING
NOROWDEPENDENCIES;
 
 
 
 
 
CREATE TABLE FB_ORACLE.CTBRESPONSAVEL
(
  PK_RESPONSAVEL NUMBER,
  NOME VARCHAR2(60),
  CPF CHAR(14),
  RG CHAR(20),
  CRC VARCHAR2(15),
  FK_CRCUF CHAR(2),
  CARGO VARCHAR2(50),
  DESCRITIVOASSINATURA VARCHAR2(50),
  TELEFONE VARCHAR2(15),
  FAX VARCHAR2(15),
  RAMAL NUMBER,
  DDD NUMBER NOT NULL,
  CONTADOR CHAR(1) DEFAULT ''  NOT NULL,
  SOCIO CHAR(1) DEFAULT ''  NOT NULL,
  EMAIL VARCHAR2(60),
  ATIVO CHAR(1) DEFAULT ''  NOT NULL,
  CONSTRAINT PCTBRESPONSAVEL
  PRIMARY KEY (
    PK_RESPONSAVEL
  )
  USING INDEX
  PCTFREE 10
  INITRANS 2
  STORAGE (
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
  )
  TABLESPACE USERS ENABLE VALIDATE
)
NOPARALLEL
PCTFREE 10
INITRANS 1
STORAGE (
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
TABLESPACE USERS
LOGGING
NOCACHE
MONITORING
NOROWDEPENDENCIES;
 
 
 
 
 
CREATE TABLE FB_ORACLE.CTBPERFIL
(
  PK_PERFIL NUMBER,
  DATAABERTURA DATE NOT NULL,
  NUMEROREGISTRO VARCHAR2(11),
  NOMEORGAOREGISTRO VARCHAR2(60),
  CNPJ CHAR(18),
  CEI CHAR(18),
  CPF CHAR(14),
  FK_MATRIZ NUMBER,
  DESCRICAORESULTADO NUMBER DEFAULT 0   NOT NULL,
  ZERARDESCRICAOLANCAMENTO CHAR(1) DEFAULT '',
  POSICIONARCURSORPRIMEIRODIGITO CHAR(1) DEFAULT '',
  ZERARCONTALANCAMENTO CHAR(1) DEFAULT '',
  NUMERODOCUMENTOAUTOMATICO CHAR(1) DEFAULT ''  NOT NULL,
  NUMEROMAXIMOPAGINA NUMBER DEFAULT 0   NOT NULL,
  UTILIZARDATADEFLACAO CHAR(1) DEFAULT ''  NOT NULL,
  TIPODATARELATORIO CHAR(1) DEFAULT '',
  IDRAZAO CHAR(3),
  CONSTRAINT PCTBPERFIL
  PRIMARY KEY (
    PK_PERFIL
  )
  USING INDEX
  PCTFREE 10
  INITRANS 2
  STORAGE (
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
  )
  TABLESPACE USERS ENABLE VALIDATE
)
NOPARALLEL
PCTFREE 10
INITRANS 1
STORAGE (
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
TABLESPACE USERS
LOGGING
NOCACHE
MONITORING
NOROWDEPENDENCIES;

Open in new window

MikeOM_DBA

Don't know your oracle version, but mine (9i) does not like the spaces between the subselect statements.

MikeOM_DBA

I meant "empty" lines.

lamishael

ASKER

Its Oracle Database 10g Express Edition

paquicuba

What application are you using to run those DDLs?

Are you getting any type of feedback from the application? (i.e. Line number causing the error)

lamishael

ASKER

EMS SQL Manager for Oracle 2007. All it says is the "ORA-00904: string: invalid identifier" error message.

lamishael

ASKER

Same thing when running from Oracle XE web interface.

paquicuba

you'recreating a View not a Table:

CREATE VIEW FB_ORACLE.VIEW_CTBCLIENTECONTABIL
AS
SELECT EXE.REFERENCIA,
EXE.FK_PLANOCONTA,
EXE.DESCRICAO,
EXE.RAZAOSOCIALCONTABIL,
EXE.DATAINICIO,
EXE.DATATERMINO,
EXE.FK_EXERCICIOSTATUS,
EXE.IE,
EXE.CEP,
EXE.ENDERECO,
EXE.NUMERO,
EXE.COMPLEMENTO,
EXE.BAIRRO,
MUN.MUNICIPIO,
EXE.FK_UF,
PER.PK_PERFIL,
PER.DATAABERTURA,
PER.NUMEROREGISTRO,
PER.NOMEORGAOREGISTRO,
PER.CNPJ,
PER.CEI,
PER.CPF,
(SELECT RES.NOME
FROM FB_ORACLE.CTBRESPONSAVEL RES
INNER JOIN
FB_ORACLE.CTBRESPONSAVELEXERCICIO REX ON (EXE.PK_EXERCICIO = REX.FK_EXERCICIO)
WHERE REX.FK_RESPONSAVEL = RES.PK_RESPONSAVEL
AND RES.SOCIO = '1'
AND RES.ATIVO = '1' AND ROWNUM = 1) AS "NOMERESPONSAVEL",

(SELECT RES.CPF
FROM FB_ORACLE.CTBRESPONSAVEL RES
INNER JOIN
FB_ORACLE.CTBRESPONSAVELEXERCICIO REX ON (EXE.PK_EXERCICIO = REX.FK_EXERCICIO)
WHERE REX.FK_RESPONSAVEL = RES.PK_RESPONSAVEL
AND RES.SOCIO = '1'
AND RES.ATIVO = '1' AND ROWNUM = 1) AS "CPFRESPONSAVEL",

(SELECT RES.RG
FROM FB_ORACLE.CTBRESPONSAVEL RES
INNER JOIN
FB_ORACLE.CTBRESPONSAVELEXERCICIO REX ON (EXE.PK_EXERCICIO = REX.FK_EXERCICIO)
WHERE REX.FK_RESPONSAVEL = RES.PK_RESPONSAVEL
AND RES.SOCIO = '1'
AND RES.ATIVO = '1' AND ROWNUM = 1) AS "RGRESPONSAVEL",

(SELECT RES.CARGO
FROM FB_ORACLE.CTBRESPONSAVEL RES
INNER JOIN
FB_ORACLE.CTBRESPONSAVELEXERCICIO REX ON (EXE.PK_EXERCICIO = REX.FK_EXERCICIO)
WHERE REX.FK_RESPONSAVEL = RES.PK_RESPONSAVEL
AND RES.SOCIO = '1'
AND RES.ATIVO = '1' AND ROWNUM = 1) AS "CARGORESPONSAVEL",

(SELECT CON.NOME
FROM FB_ORACLE.CTBRESPONSAVEL CON
INNER JOIN
FB_ORACLE.CTBRESPONSAVELEXERCICIO REX ON (EXE.PK_EXERCICIO = REX.FK_EXERCICIO)
WHERE REX.FK_RESPONSAVEL = CON.PK_RESPONSAVEL
AND CON.CONTADOR = '1'
AND CON.ATIVO = '1' AND ROWNUM = 1) AS "NOMECONTADOR",

(SELECT CON.CRC
FROM FB_ORACLE.CTBRESPONSAVEL CON
INNER JOIN
FB_ORACLE.CTBRESPONSAVELEXERCICIO REX ON (EXE.PK_EXERCICIO = REX.FK_EXERCICIO)
WHERE REX.FK_RESPONSAVEL = CON.PK_RESPONSAVEL
AND CON.CONTADOR = '1'
AND CON.ATIVO = '1' AND ROWNUM = 1) AS "CRCCONTADOR",

(SELECT CON.FK_CRCUF
FROM FB_ORACLE.CTBRESPONSAVEL CON
INNER JOIN
FB_ORACLE.CTBRESPONSAVELEXERCICIO REX ON (EXE.PK_EXERCICIO = REX.FK_EXERCICIO)
WHERE REX.FK_RESPONSAVEL = CON.PK_RESPONSAVEL
AND CON.CONTADOR = '1'
AND CON.ATIVO = '1' AND ROWNUM = 1) AS "UFCRCCONTADOR"
FROM FB_ORACLE.CTBEXERCICIO EXE
INNER JOIN
FB_ORACLE.CTBPERFIL PER ON (EXE.FK_PERFIL = PER.PK_PERFIL)
INNER JOIN
FB_ORACLE.ALLMUNICIPIO MUN ON (EXE.FK_MUNICIPIO = MUN.PK_MUNICIPIO);

lamishael

ASKER

I forgot to say, the errors states that EXE.PK_EXERCICIO is an invalid identifier.

lamishael

ASKER

I´m away from the pc running this DB now, but I'll try your solution when I get there.

paquicuba

I can see that column PK_EXERCICIO exists in the table specification you posted. Is the column in question part of the table in your echema?

paquicuba

If I select:

ALEX@DEV> select ff from dual;
select ff from dual
*
ERROR at line 1:
ORA-00904: "FF": invalid identifier

I get the same error...

lamishael

ASKER

So what should I then? All the tables are there, with the fields that the view needs, its just doesnt find them!

ASKER CERTIFIED SOLUTION

paquicuba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

paquicuba

Pretty much, Oracle is telling you that only join keys pertaining to the tables being INNER JOINed (1999 ANSI Syntax) can be used within the ON( ) clause. If you need to reference a column from an enclosing query, you must then use the WHERE clause.

In the above example, EXE.PK_EXERCICIO column doesn't exist in FB_ORACLE.CTBRESPONSAVEL CON table nor it exists in FB_ORACLE.CTBRESPONSAVELEXERCICIO table.

lamishael

ASKER

Thanks a lot!