Solved

ORA-00904: string: invalid identifier

Posted on 2008-09-30
15
2,465 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 300 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

724 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