CODEWITHSUNDEEP
sqloracle-databasealias
Tony D
Tony D

Reputation: 25

how to reference an alias in an oracle nested query?

I have a couple of nested queries like this:

(SELECT "impartidas"."idUsuarioProf"
   FROM "impartidas"
   WHERE "impartidas"."periodo" = "periodoPlanGrado"."periodo" and
         "impartidas"."idMateria" = "materiasPlan"."idMateria") T,
(SELECT "usuarios"."apellidoPaterno" || ' , ' || "usuarios"."nombres"
   FROM "usuarios"
   WHERE "usuarios"."idUsuario" = 36) as "nomprofesor"

The first one outputs the teacher ID in a column named T.

What do I need to change in the second query, just so that instead of 36, it uses the value that was shown in column aliased T?

In short I need to perform the second query, based on the output ID value of the first query.

Upvotes: 1

Views: 8302

Answers (2)

Szilard Barany
Szilard Barany

Reputation: 1135

What about this:

SELECT "usuarios"."apellidoPaterno" || ' , ' || "usuarios"."nombres" AS "nomprofesor"
FROM   "usuarios"
WHERE  "usuarios"."idUsuario" = (
           SELECT "impartidas"."idUsuarioProf"
           FROM   "impartidas", "periodoPlanGrando", "materiasPlan"
           WHERE  "impartidas"."periodo" = "periodoPlanGrado"."periodo" 
              AND "impartidas"."idMateria" = "materiasPlan"."idMateria"
        )

or maybe

SELECT "usuarios"."apellidoPaterno" || ' , ' || "usuarios"."nombres" AS "nomprofesor"
FROM   "usuarios"
WHERE  "usuarios"."idUsuario" IN (
           SELECT "impartidas"."idUsuarioProf"
           FROM   "impartidas", "periodoPlanGrando", "materiasPlan"
           WHERE  "impartidas"."periodo" = "periodoPlanGrado"."periodo" 
              AND "impartidas"."idMateria" = "materiasPlan"."idMateria"
        )

if multiple rows might be generated by the subquery (I do not know the schema and my Spanish is not very good (IS NULL) to understand what might be in the "impartidas" table).

For code maintenance and readability reasons I would write this:

SELECT u.apellidoPaterno || ' , ' || u.nombres AS nomprofesor
FROM   usuarios u
WHERE  u.idUsuario = (
           SELECT i.idUsuarioProf
           FROM   impartidas i
                  INNER JOIN periodoPlanGrando p USING ( periodo )
                  INNER JOIN materiasPlan m      USING ( idMateria )
        -- WHERE (other condifitions)
        )

or even this:

SELECT u.apellido_paterno || ' , ' || u.nombres AS nom_profesor
FROM   usuarios u
WHERE  u.id_usuario = (
           SELECT i.id_usuario_prof
           FROM   impartidas i
                  INNER JOIN periodo_plan_grado p USING ( periodo )
                  INNER JOIN materias_plan m      USING ( id_materia )
        -- WHERE (other condifitions)
        )

but this would require refractoring table and column names to be more Oracle identifier like.

Upvotes: 0

APC
APC

Reputation: 146199

r In the absence of any context it's difficult to understand why you're taken such a convoluted approach. The obvious approach is just a straightforward join:

SELECT "impartidas"."idUsuarioProf"    
 ,  "usuarios"."apellidoPaterno" || ' , ' || "usuarios"."nombres" "nomprofesor" 
FROM "impartidas"   
      , "periodoPlanGrado"
      , "materiasPlan"
      ,  "usuarios"
 WHERE "impartidas"."periodo" = "periodoPlanGrado"."periodo" 
and          "impartidas"."idMateria" = "materiasPlan"."idMateria") T
and "usuarios"."idUsuario" = "impartidas"."idUsuarioProf" 
/

But if you really need the inlining then you would need to do the joining externally, something like this:

select P."nomprofesor" 
from 
    (SELECT "impartidas"."idUsuarioProf"
      FROM "impartidas"   
              , "periodoPlanGrado"
              , "materiasPlan"               
     WHERE "impartidas"."periodo" = "periodoPlanGrado"."periodo" 
      and      "impartidas"."idMateria" = "materiasPlan"."idMateria") T, 
    (SELECT "usuarios"."apellidoPaterno" || ' , ' || "usuarios"."nombres"  as "nomprofesor" 
            , "usuarios"."idUsuario"
      FROM "usuarios"       ) P     
WHERE P."idUsuario" = T."idUsuarioProf"

Note that you need to include all the joining columns in the projection of each sub-query. As, you need to use an aliases to reference a derived column in the outer query.

Upvotes: 2

Related Questions