Error Coalesce cant link inside Inner Join

Question

Actually i have a Oracle 10 and MSSQL Databases where i use this query:

SELECT F1.ID_ACTIVO, F1.DENOMINACION, F1.IMPACTO_TOTAL, F1.RIESGO_TOTAL, AIRR.ID_RANGO, SUM(IMPACTORESIDUAL), SUM(RIESGORESIDUAL) 
FROM (  SELECT AA.ID_TIPOACTIVO, AA.ID_ACTIVO, A.DENOMINACION, SUM(AITA.IMPACTO_TOTAL) AS IMPACTO_TOTAL, SUM(AITA.RIESGO_TOTAL) AS RIESGO_TOTAL 
        FROM PROCESOS A 
        INNER JOIN AGR_ACTIVO AA ON (AA.ID_TIPOACTIVO = 2 AND AA.ID_ACTIVO = A.ID_PROCESO) 
        INNER JOIN AGR_IMP_TOTAL_ACT AITA ON (AA.ID_TIPOACTIVO = AITA.ID_TIPOACTIVO AND AA.ID_ACTIVO = AITA.ID_ACTIVO)  
        GROUP BY AA.ID_TIPOACTIVO, AA.ID_ACTIVO, A.DENOMINACION) F1 
INNER JOIN AGR_IMP_RISK_REAL AIRR ON (F1.ID_TIPOACTIVO = AIRR.ID_TIPOACTIVO AND F1.ID_ACTIVO = AIRR.ID_ACTIVO) 
GROUP BY F1.ID_ACTIVO, F1.DENOMINACION, F1.IMPACTO_TOTAL, F1.RIESGO_TOTAL, AIRR.ID_RANGO 
ORDER BY F1.RIESGO_TOTAL 
ASC,F1.ID_ACTIVO, F1.DENOMINACION, AIRR.ID_RANGO

This returns a table like this:

|ID_ACTIVO | DENOMINACION           | IMPACTO_TOTAL | RIESGO_TOTAL | ID_RANGO | column6 | column7|
|----------|------------------------|---------------|--------------|----------|---------|--------|
| 20845    |Consultoría de Seguridad|     15375     |    2464,375  |     1    |  46125  |7393,125|

This table represents the elements with ID_TIPOACTIVO = 2. Now i have more elements with ID_TIPOACTIVO with values from 1 to 14. So i created the follow query but dont work, my problem is the line INNER JOIN AGR_IMP_RISK_REAL AIRR ON (COALESCE(F1.ID_TIPOACTIVO,F2.ID_TIPOACTIVO) = AIRR.ID_TIPOACTIVO AND COALESCE(F1.ID_ACTIVO,F2.ID_ACTIVO) = AIRR.ID_ACTIVO) not link F1.ID_TIPOACTIVO and F1.ID_ACTIVO from the COALESCE() statement (i marked the line with **). How i can solve this problem? (taking care, because I have to add that after 14 subquerys).

This is the error (in AquaData, using the Database from MSSQL):

[Error] Script lines: 1-19 ------------------------- El identificador formado por varias partes "F1.ID_TIPOACTIVO" no se pudo enlazar.

[Error] Script lines: 1-19 ------------------------- El identificador formado por varias partes "F1.ID_TIPOACTIVO" no se pudo enlazar.

[Error] Script lines: 1-19 ------------------------- El identificador formado por varias partes "F1.ID_ACTIVO" no se pudo enlazar.

[Error] Script lines: 1-19 ------------------------- El identificador formado por varias partes "F1.ID_ACTIVO" no se pudo enlazar.

And this is the error in Oracle:

[Error] Script lines: 1-19 ------------------------- ORA-00904: "F1"."ID_ACTIVO": invalid identifier

Here is the query:

SELECT COALESCE(F1.ID_ACTIVO,F2.ID_ACTIVO), COALESCE(F1.NOMBRE,F2.DENOMINACION), COALESCE(F1.IMPACTO_TOTAL,F2.IMPACTO_TOTAL), COALESCE(F1.RIESGO_TOTAL,F2.RIESGO_TOTAL), AIRR.ID_RANGO, SUM(IMPACTORESIDUAL), SUM(RIESGORESIDUAL) 
FROM
//IF ID_TIPOACT=1
(   SELECT AA.ID_TIPOACTIVO, AA.ID_ACTIVO, A.NOMBRE, SUM(AITA.IMPACTO_TOTAL) AS IMPACTO_TOTAL, SUM(AITA.RIESGO_TOTAL) AS RIESGO_TOTAL 
            FROM PRODUCTOS A 
            INNER JOIN AGR_ACTIVO AA ON (AA.ID_TIPOACTIVO = 1 AND AA.ID_ACTIVO = A.ID_PRODUCTO) 
            INNER JOIN AGR_IMP_TOTAL_ACT AITA ON (AA.ID_TIPOACTIVO = AITA.ID_TIPOACTIVO AND AA.ID_ACTIVO = AITA.ID_ACTIVO)  
            GROUP BY AA.ID_TIPOACTIVO, AA.ID_ACTIVO, A.NOMBRE) F1 , 
//IF ID_TIPOACT=2
        (   SELECT AA.ID_TIPOACTIVO, AA.ID_ACTIVO, A.DENOMINACION, SUM(AITA.IMPACTO_TOTAL) AS IMPACTO_TOTAL, SUM(AITA.RIESGO_TOTAL) AS RIESGO_TOTAL 
            FROM PROCESOS A 
            INNER JOIN AGR_ACTIVO AA ON (AA.ID_TIPOACTIVO = 2 AND AA.ID_ACTIVO = A.ID_PROCESO) 
            INNER JOIN AGR_IMP_TOTAL_ACT AITA ON (AA.ID_TIPOACTIVO = AITA.ID_TIPOACTIVO AND AA.ID_ACTIVO = AITA.ID_ACTIVO)  
            GROUP BY AA.ID_TIPOACTIVO, AA.ID_ACTIVO, A.DENOMINACION) F2 
**INNER JOIN AGR_IMP_RISK_REAL AIRR ON (COALESCE(F1.ID_TIPOACTIVO,F2.ID_TIPOACTIVO) = AIRR.ID_TIPOACTIVO AND COALESCE(F1.ID_ACTIVO,F2.ID_ACTIVO) = AIRR.ID_ACTIVO) 
GROUP BY COALESCE(F1.ID_ACTIVO,F2.ID_ACTIVO), COALESCE(F1.NOMBRE,F2.DENOMINACION), COALESCE(F1.IMPACTO_TOTAL,F2.IMPACTO_TOTAL), COALESCE(F1.RIESGO_TOTAL,F2.RIESGO_TOTAL), AIRR.ID_RANGO
ORDER BY COALESCE(F1.RIESGO_TOTAL,F2.RIESGO_TOTAL)
ASC,COALESCE(F1.ID_ACTIVO,F2.ID_ACTIVO), COALESCE(F1.NOMBRE,F2.DENOMINACION), AIRR.ID_RANGO

Thank you in advance.

Answer 1

I hope this will help

Query 1 (like yours)

with 
f1 as (select 1 n from dual),
f2 as (select 1 m from dual),
agr_imp_risk_real as (select 1 k from dual)
select t1.* from f1, f2 inner join agr_imp_risk_real airr  on (coalesce(f1.n, f2.m) = coalesce(f2.m, airr.k));

Output: ORA-00904: "F1"."N": invalid identifier

Query 2

with 
f1 as (select 1 n from dual),
f2 as (select 1 m from dual),
agr_imp_risk_real as (select 1 k from dual)
select f1.* from f1, f2, agr_imp_risk_real airr where coalesce(f1.n, f2.m) = coalesce(f2.m, airr.k);

Query result: 1

So don't use INNER JOIN keywords or don't join subqueries by using comma and keywords simultaneously (Actually, I don't know the theory regarding this issue. I've never tried to mix both approaches in one query)