Solved

ORA-00904: string: invalid identifier

Posted on 2008-09-30
15
2,391 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now