SQL: invalid identifier

Question

I need some help with SQL:

SELECT
    servidor,
    matricula,
    funcao,
    TO_CHAR(admissao, 'dd/mm/yyyy'),
    vatagens,
    escolaridade
FROM
    (
        SELECT DISTINCT
            pf.nome        AS servidor,
            m.matricula    AS matricula,
            ca.descricao   AS funcao,
            TO_CHAR(c.dataadmissao, 'dd/mm/yyyy') AS "admissao",
            LISTAGG(if.valorreferencia
                    || '-'
                    || ev.descricaoreduzida, ';') WITHIN GROUP(
                ORDER BY
                    if.valorreferencia ASC
            ) AS vantagens,
            ne.descricao   AS escolaridade
        FROM
            pessoafisica               pf
            INNER JOIN matriculafp                m ON m.pessoa_id = pf.id
            INNER JOIN vinculofp                  v ON m.id = v.matriculafp_id
            INNER JOIN fichafinanceirafp          ff ON v.id = ff.vinculofp_id
            INNER JOIN itemfichafinanceirafp      if ON ff.id = if.fichafinanceirafp_id
            INNER JOIN eventofp                   ev ON ev.id = if.eventofp_id
            INNER JOIN folhadepagamento           fp ON ff.folhadepagamento_id = fp.id
            INNER JOIN contratofp                 c ON v.id = c.id
            INNER JOIN cargo                      ca ON c.cargo_id = ca.id
            INNER JOIN lotacaofuncional           lf ON v.id = lf.vinculofp_id
            INNER JOIN unidadeorganizacional      und ON lf.unidadeorganizacional_id = und.id
            INNER JOIN hierarquiaorganizacional   horg ON und.id = horg.subordinada_id
            LEFT JOIN nivelescolaridade          ne ON pf.nivelescolaridade_id = ne.id
        WHERE
            if.mes = 7
            AND if.ano = 2019
            AND horg.descricao = 'Secretaria Municipal de Educação - SEME' 
            --AND ev.id = if.eventofp_id
    )
GROUP BY
    servidor,
    matricula,
    funcao,
    admissao,
    vatagens,
    escolaridade

This is the error:

ORA-00904: "VATAGENS": identificador inválido 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 47 Column: 5

One obvious problem: Your subquery (which ends immediately before GROUP BY) does not have an alias. All derived tables require an alias in Oracle. — Tim Biegeleisen
vatagens column is not in the subquery, however, used in outer query. In subquery column is vantagens — Tajinder

Tajinder Tajinder · Accepted Answer · 2019-07-23T15:38:06

Please check below query.

Changes: Column name vantagens corrected in outer query.

SELECT
    servidor,
    matricula,
    funcao,
    TO_CHAR(admissao, 'dd/mm/yyyy'),
    vantagens,
    escolaridade
FROM
    (
        SELECT DISTINCT
            pf.nome        AS servidor,
            m.matricula    AS matricula,
            ca.descricao   AS funcao,
            TO_CHAR(c.dataadmissao, 'dd/mm/yyyy') AS admissao,
            LISTAGG(if.valorreferencia
                    || '-'
                    || ev.descricaoreduzida, ';') WITHIN GROUP(
                ORDER BY
                    if.valorreferencia ASC
            ) AS vantagens,
            ne.descricao   AS escolaridade
        FROM
            pessoafisica               pf
            INNER JOIN matriculafp                m ON m.pessoa_id = pf.id
            INNER JOIN vinculofp                  v ON m.id = v.matriculafp_id
            INNER JOIN fichafinanceirafp          ff ON v.id = ff.vinculofp_id
            INNER JOIN itemfichafinanceirafp      if ON ff.id = if.fichafinanceirafp_id
            INNER JOIN eventofp                   ev ON ev.id = if.eventofp_id
            INNER JOIN folhadepagamento           fp ON ff.folhadepagamento_id = fp.id
            INNER JOIN contratofp                 c ON v.id = c.id
            INNER JOIN cargo                      ca ON c.cargo_id = ca.id
            INNER JOIN lotacaofuncional           lf ON v.id = lf.vinculofp_id
            INNER JOIN unidadeorganizacional      und ON lf.unidadeorganizacional_id = und.id
            INNER JOIN hierarquiaorganizacional   horg ON und.id = horg.subordinada_id
            LEFT JOIN nivelescolaridade          ne ON pf.nivelescolaridade_id = ne.id
        WHERE
            if.mes = 7
            AND if.ano = 2019
            AND horg.descricao = 'Secretaria Municipal de Educação - SEME' 
            --AND ev.id = if.eventofp_id
    )
GROUP BY
    servidor,
    matricula,
    funcao,
    admissao,
    vantagens,
    escolaridade

SQL: invalid identifier

1 Answers