Mother class of an outer join not visible (ORACLE)

Question

Mother class of an outer join not visible (ORACLE)

Navigation

#1 by (0 votes)

0

Good morning,

I have this select:

select 
    count(ob.obj_id) as appeareances,
    cl.id as classs,
    cl.intl_id as class_intl,
    cl.name as class_name,
    cf.name as classif_name,
    cf.intl_id as classif_intl,
    cf.id as classif  
from 
    code_obj_class cl,
    code_obj_classif cf   
    left outer join obj_class2 ob on 
        ob.obj_class_id = cl.id and 
        ob.obj_Classif_id = cf.id and 
        ob.end_Date > to_date('21.06.2017','DD.MM.YYYY')  
group by 
    classs,
    class_intl,
    class_name,
    classif_name,
    classif_intl,
    classif  
order by appeareances asc;

I do not understand why it does not recognize me "ob.obj_class_id = cl.id", specifically CL.ID. This is the error message:

ORA-00904: "CL"."ID": identificador no válido
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error en la línea: 5, columna: 53

Any ideas?

edit:

select count(ob.obj_id) as appeareances,
    cl.id as classs,
    cl.intl_id as class_intl,
    cl.name as class_name,
    cf.name as
    classif_name,
    cf.intl_id as classif_intl,
    cf.id as classif  
from
    code_obj_class cl,
    code_obj_classif cf,
    obj_class2 ob 
where
    ob.obj_class_id = cl.id and ob.obj_Classif_id = cf.id and 
    ob.end_Date > to_date('21.06.2017', 'DD.MM.YYYY')  
group by 
    cl.id,
    cl.intl_id,
    cl.name,
    cf.name,
    cf.intl_id,
    cf.id   
order by appeareances asc;

the pure select without join, only with crossing tables, with the same fields, if executed

sql oracle join

asked by Nhano 21.06.2017 в 13:00

source

1 answer

Error in TAD operations C ++ matrix Filemaker run script on the server

score 0 · Answer 1

That's it, apparently the JOIN is only with the last table of FROM . It is solved doing everything with JOIN s.

SELECT count(ob.obj_id) AS appeareances
    ,cl.id AS classs
    ,cl.intl_id AS class_intl
    ,cl.NAME AS class_name
    ,cf.NAME AS classif_name
    ,cf.intl_id AS classif_intl
    ,cf.id AS classif
FROM code_obj_class cl
JOIN code_obj_classif cf ON cl.obj_classif_id = cf.id
LEFT OUTER JOIN obj_class2 ob ON ob.obj_class_id = cl.id
    AND ob.obj_Classif_id = cf.id
    AND ob.end_Date > to_date('21.06.2017', 'DD.MM.YYYY')
GROUP BY cl.id
    ,cl.intl_id
    ,cl.NAME
    ,cf.NAME
    ,cf.intl_id
    ,cf.id
ORDER BY appeareances ASC;