INSERT INTO WITH SELECT and WHERE (Problem)

Question

I need to perform an insert into with select, but DBEAVER is returning the following error:

SQL Error [904] [42000]: ORA-00904:" NR_CARTEIRA ": invalid identifier

Does anyone know what can it be ?

INSERT INTO recem_nascido(
   DT_ATENDIMENTO ,
   CD_DECLARACAO_NASCIDO_VIVO,
   cd_atendimento,
   CD_MULTI_EMPRESA,
   cd_paciente,
   nm_paciente,
   CD_ATENDIMENTO_PAI,
   dt_nascimento,
   nm_mae,
   cd_convenio,
   NR_CARTEIRA )
SELECT
   a.DT_ATENDIMENTO ,
   c.CD_DECLARACAO_NASCIDO_VIVO,
   a.cd_atendimento,
   a.CD_MULTI_EMPRESA,
   a.cd_paciente,
   b.nm_paciente,
   a.CD_ATENDIMENTO_PAI,
   b.dt_nascimento,
   b.nm_mae,
   a.cd_convenio,
   a.NR_CARTEIRA
FROM
     DBAMV.ATENDIME a,
     dbamv.paciente b,
     dbamv.recem_nascido c
WHERE 
   a.CD_ATENDIMENTO = 33079344
   AND c.CD_DECLARACAO_NASCIDO_VIVO = 111
   AND A.NR_CARTEIRA = 321321321
   AND a.cd_atendimento_pai IS NOT NULL 
   AND a.CD_PACIENTE = b.CD_PACIENTE 
   AND c.CD_ATENDIMENTO = a.CD_ATENDIMENTO; ```

Well first guess is that you don't have a column in your target table named NR_CARTEIRA. — Andrew
Bad habits to kick: using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (almost 30 years ago) and its use is discouraged — marc_s
And along with JOIN, learn to use meaningful table aliases. The queries are much easier to read with table abbreviations rather than arbitrary letters. — Gordon Linoff
And it will take probably next 30 years than the new-style syntax can be used safely everywhere — Marmite Bomber

D. Mika D. Mika · Accepted Answer · 2021-03-22T18:15:21

There are only two options:

The target table recem_nascido has no column NR_CARTEIRA
The table DBAMV.ATENDIME has no column NR_CARTEIRA

Since you only posted the query, but not the table structure, there is no way to narrow this down further.

INSERT INTO WITH SELECT and WHERE (Problem)

1 Answers