?
Solved

ORA-00904: string: invalid identifier

Posted on 2008-09-30
15
Medium Priority
?
2,481 Views
Last Modified: 2013-12-07
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

0
Comment
Question by:lamishael
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 22606066

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

0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 22606072

I meant "empty" lines.

0
 

Author Comment

by:lamishael
ID: 22607115
Its Oracle Database 10g Express Edition
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 23

Expert Comment

by:paquicuba
ID: 22607297
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)
0
 

Author Comment

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

Author Comment

by:lamishael
ID: 22607344
Same thing when running from Oracle XE web interface.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 22607436
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);
0
 

Author Comment

by:lamishael
ID: 22607465
I forgot to say, the errors states that EXE.PK_EXERCICIO is an invalid identifier.
0
 

Author Comment

by:lamishael
ID: 22607596
I´m away from the pc running this DB now, but I'll try your solution when I get there.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 22607607
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?
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 22607612
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...
0
 

Author Comment

by:lamishael
ID: 22609876
So what should I then? All the tables are there, with the fields that the view needs, its just doesnt find them!
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 1200 total points
ID: 22614155
I see your problem,

All those inline SELECTs cannot see main query's column "EXE.PK_EXERCICIO" in the "ON" join clause, see below:

This is worng:

 (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"

This is Right:

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


You need to correct all the inline sub-selects....

0
 
LVL 23

Expert Comment

by:paquicuba
ID: 22614239
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.
0
 

Author Closing Comment

by:lamishael
ID: 31501560
Thanks a lot!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question