Invalid identifier error when using RIGHT JOIN inside FROM clause

Question

I want to use within a FROM a subset of 2 tables using RIGHT JOIN (I want from that subset all the rows of ITV2_VEHICULOS whose ID is not in ITV2_HIST_VEHICULOS) so that the SELECT "takes" the data from there and with the WHERE it can filter

My query:

SELECT
    *
FROM
    ITV2_INSPECCIONES I,
    ITV2_HORAS_INSPECCION HI_FIN,
    ITV2_INSPECCIONES I_SIG,
    ITV2_HORAS_INSPECCION HI_SIG_INI,
    ITV2_HIST_VEHICULOS VH,
    ITV2_CATEGORIAS_VEHICULO CAT,
    ITV2_CLASIF_VEH_CONS CVC,
    ITV2_CLASIF_VEH_USO CVU,
    (
        SELECT
            *
        FROM
            ITV2_HIST_VEHICULOS VH
        RIGHT JOIN ITV2_VEHICULOS V ON
            VH.C_VEHICULO_ID = V.C_VEHICULO_ID
    ) VI 

WHERE
    I.C_TIPO_INSPECCION = 1     
    AND I.F_DESFAVORABLE IS NOT NULL
    AND I.C_RESULTADO IN(
        3,
        4
    )
    AND I.C_VEHICULO_ID = VI.C_VEHICULO_ID 
    AND VI.C_CATEGORIA_ID = CAT.C_CATEGORIA_ID
    AND VI.C_CLASIF_VEH_CONS_ID = CVC.C_CLASIF_VEH_CONS_ID
    AND VI.C_CLASIF_VEH_USO_ID = CVU.C_CLASIF_VEH_USO_ID -- HORAS  
    AND I.C_ESTACION_ID = HI_FIN.C_ESTACION_ID
    AND I.C_INSPECCION_ID = HI_FIN.C_INSPECCION_ID
    AND I.N_ANNO = HI_FIN.N_ANNO
    AND HI_FIN.C_TIPO_HORA_ID = 6 -- INSPECCION SIGUIENTE   
    AND I.C_ESTACION_ID = I_SIG.C_ESTACION_ID_FASE_ANT
    AND I.C_INSPECCION_ID = I_SIG.C_INSPECCION_ID_FASE_ANT
    AND I.N_ANNO = I_SIG.N_ANNO_FASE_ANT --    
    AND I_SIG.N_ANNO IN(
        2013,
        2014,
        2015,
        2016,
        2017,
        2018
    )
    AND I_SIG.C_ESTACION_ID IN(
        3,
        21,
        22,
        26,
        28,
        32,
        34,
        37,
        41,
        47,
        53,
        59,
        60
    )
    AND I_SIG.F_INSPECCION >= '01/09/2015'
    AND I_SIG.F_INSPECCION <= '30/09/2018' --

    AND I_SIG.F_DESFAVORABLE IS NULL
    AND I_SIG.C_RESULTADO IN(
        1,
        2
    ) -- Y HORAS

    AND I_SIG.C_ESTACION_ID = HI_SIG_INI.C_ESTACION_ID
    AND I_SIG.C_INSPECCION_ID = HI_SIG_INI.C_INSPECCION_ID
    AND I_SIG.N_ANNO = HI_SIG_INI.N_ANNO
    AND HI_SIG_INI.C_TIPO_HORA_ID = 1

 --GROUP BY...

I expect in the output:

C_ESTACION_ID(FROM I) |C_VEHICULO_ID(FROM(I) |C_TIPO_HORA_ID(FROM HI_FIN)|F_HORA (FROM I_FIN) |A_MATRICULA FROM (V) | F_CAMBIO FROM (VH -> IF subdata of V EXISTS)
 ---------------------|----------------------|---------------------------|--------------------|---------------------|---------------------------------------

**stop** using commas between table names in the from clause, use explicit ANSI join syntax instead. — Paul Maxwell, Nov 16 '18 at 06:59
We have no idea what would be an invalid identifier from that query. We would need to see all the table DDL. What is the **complete** error message? — Paul Maxwell, Nov 16 '18 at 07:01
@Used_By_Already I just tried it in another scheme and it works correctly. Would not I have "permission" to run joins in the other scheme? Sorry, I'm semi-novice in sql — Raulitus, Nov 16 '18 at 07:08
Maybe, I cannot be certain. Sounds possible. But I **implore** you to learn how to use "inner join" "left join" "cross join". — Paul Maxwell, Nov 16 '18 at 07:18
Add some sample table data and the expected result - all as formatted text, not images. Before you spend too much time, take a look at https://stackoverflow.com/help/mcve. — jarlh, Nov 16 '18 at 07:53

score 1 · Answer 1 · answered Nov 16 '18 at 07:23

This is what your query would look like if you use "explicit join syntax" instead of just some commas between table names:

SELECT *
FROM ITV2_INSPECCIONES I
INNER JOIN ITV2_HORAS_INSPECCION HI_FIN ON I.C_ESTACION_ID = HI_FIN.C_ESTACION_ID
     AND I.C_INSPECCION_ID = HI_FIN.C_INSPECCION_ID
     AND I.N_ANNO = HI_FIN.N_ANNO
INNER JOIN ITV2_INSPECCIONES I_SIG ON I.C_ESTACION_ID = I_SIG.C_ESTACION_ID_FASE_ANT
     AND I.C_INSPECCION_ID = I_SIG.C_INSPECCION_ID_FASE_ANT
     AND I.N_ANNO = I_SIG.N_ANNO_FASE_ANT
INNER JOIN ITV2_HORAS_INSPECCION HI_SIG_INI ON I_SIG.C_ESTACION_ID = HI_SIG_INI.C_ESTACION_ID
     AND I_SIG.C_INSPECCION_ID = HI_SIG_INI.C_INSPECCION_ID
     AND I_SIG.N_ANNO = HI_SIG_INI.N_ANNO
WHERE I.C_TIPO_INSPECCION = 1
    AND I.F_DESFAVORABLE IS NOT NULL
    AND I.C_RESULTADO IN (3, 4)
    AND HI_FIN.C_TIPO_HORA_ID = 6 -- INSPECCION SIGUIENTE   
    AND HI_SIG_INI.C_TIPO_HORA_ID = 1
    AND I_SIG.F_INSPECCION >= '01/09/2015'
    AND I_SIG.F_INSPECCION <= '30/09/2018'
    AND I_SIG.F_DESFAVORABLE IS NULL
    AND I_SIG.N_ANNO IN (2013, 2014, 2015, 2016, 2017, 2018)
    AND I_SIG.C_ESTACION_ID IN (3, 21, 22, 26, 28, 32, 34, 37, 41, 47, 53, 59, 60)
    AND I_SIG.C_RESULTADO IN (1, 2) -- Y HORAS

Now I had to pull out several tables and the subquery from that because, frankly, they don't make much sense to me:

ITV2_HIST_VEHICULOS VH,         << no join conditions to preceding tables
ITV2_CATEGORIAS_VEHICULO CAT,   << no join conditions to preceding tables
ITV2_CLASIF_VEH_CONS CVC,       << no join conditions to preceding tables
ITV2_CLASIF_VEH_USO CVU,        << no join conditions to preceding tables
(
    SELECT
        *
    FROM ITV2_VEHICULOS V
    LEFT JOIN ITV2_HIST_VEHICULOS VH ON
        VH.C_VEHICULO_ID = V.C_VEHICULO_ID
) VI
AND I.C_VEHICULO_ID = VI.C_VEHICULO_ID 
AND VI.C_CATEGORIA_ID = CAT.C_CATEGORIA_ID
AND VI.C_CLASIF_VEH_CONS_ID = CVC.C_CLASIF_VEH_CONS_ID
AND VI.C_CLASIF_VEH_USO_ID = CVU.C_CLASIF_VEH_USO_ID

But I need the relation `ITV2_INSPECCIONES->ITV2_VEHICULOS->ITV2_HIST_VEHICULOS` How can I relate them? — Raulitus, Nov 16 '18 at 08:15
I have as much chance of doing what you ask as divining what the next lottery numbers will be. What I have done for you is identified where your current query starts to breakdown, but I know **nothing** about your tables I can go no further. — Paul Maxwell, Nov 16 '18 at 08:24
They are tables with a lot of data, I do not fit in SO. I will try to synthesize it — Raulitus, Nov 16 '18 at 08:30
@Raulitus: you don't need to show the data in the tables. You need to show the table definitions (columns) and which column in one table relates to a column in another table (e.g. foreign keys) - those will be the columns needed for the join condition — , Nov 16 '18 at 08:49

Invalid identifier error when using RIGHT JOIN inside FROM clause

1 Answers1