Ambigously defined column in a subquery-CodePudding

I've the following subquery in an sql query:

(
 SELECT ID_PLAN, ID_CURSO, NEDICION, NOMBRE AS NOMBREUNIDAD FROM ASISTEN, ALUMNOS, UNIDADES
     WHERE ASISTEN.COD = ALUMNOS.COD AND UNIDADES.IDESTRUCTURA = ALUMNOS.IDESTRUCTURA
     AND UNIDADES.CDUNDORG = ALUMNOS.CDUNDORG
     AND UPPER(TRANSLATE(UNIDADES.NOMBRE, 'áéíóúÁÉÍÓÚ', 'aeiouAEIOU')) LIKE '%CONSEJERIA%'
     GROUP BY ID_PLAN, ID_CURSO, NEDICION) ASIS

Problem I have I believe lies in that both table ALUMNOS and UNIDADES have a column named 'NOMBRE' so if I attempt to execute the query I obtain:

00000 - "column ambiguously defined"

To avoid that I thought about changing NOMBRE AS NOMBREUNIDAD to:

 UNIDADES.NOMBRE AS NOMBREUNIDAD

But if I do that I get a:

00000 - "not a GROUP BY expression"

So, I don't know what to do so that subquery executes properly.

What should I change to properly execute query without changing the column name?

CodePudding user response：

Aliases are pretty useful, if you use them. The simplify queries and make them easier to read and maintain. I'd suggest you to do so, as it'll also help query to work because Oracle doesn't know which table you actually meant when you selected those 4 columns - which tables do they belong to?

This is just a guess as I don't know your tables so you'll have to fix it yourself. Also, I literally JOINed tables; try to avoid comma-separating them in FROM clause and doing join in WHERE clause as it is supposed to filter data.

GROUP BY, as already commented, is probably useless. If you wanted to fetch distinct set of values, then use appropriate keyword: distinct.

SELECT DISTINCT n.id_plan,
                s.id_curso,
                u.nedicion,
                u.nombre
  FROM asisten n
       JOIN alumnos s ON n.cod = s.cod
       JOIN unidades u
          ON     u.idestructura = s.idestructura
             AND u.cdundorg = s.cdundorg
 WHERE UPPER (TRANSLATE (u.nombre, 'áéíóúÁÉÍÓÚ', 'aeiouAEIOU')) LIKE '%CONSEJERIA%'

CodePudding user response：

I managed to solve my problem:

(
 SELECT ID_PLAN, ID_CURSO, NEDICION, UNIDADES.NOMBRE AS NOMBREUNIDAD
 FROM ASISTEN, ALUMNOS, UNIDADES
 WHERE ASISTEN.COD = ALUMNOS.COD AND UNIDADES.IDESTRUCTURA = ALUMNOS.IDESTRUCTURA
 AND UNIDADES.CDUNDORG = ALUMNOS.CDUNDORG
 AND UPPER(TRANSLATE(UNIDADES.NOMBRE, 'áéíóúÁÉÍÓÚ', 'aeiouAEIOU')) LIKE '%CONSEJERIA%'
 GROUP BY UNIDADES.NOMBRE,ID_PLAN, ID_CURSO, NEDICION
 )