select * from (
select
    carreras.nro_carrera,
    carreras.nombre as nombre_carrera,
    carreras.nombre_largo as nombre_largo_carrera,
    carreras.nombre_administrativo as nombre_administrativo_carrera,
    carreras.administrativo,
    carreras.condicional,
    carreras.inhabilitado as inhabilitado_carrera,
    null as nro_plan,
    null as nombre_plan,
    null as duracion_cursada,
    null as rendidas_cantidad,
    null as nota_aprobacion,
    null as nota_promocion,
    null as nota_aprobacion_libre,
    null as comisiones_rendidas,
    null as comisiones_duracion,
    null as inhabilitado_plan,
    null as nro_materia,
    null as nombre_materia,
    null as nombre_largo,
    null as curso,
    null as explan,
    null as nota,
    null as analitico,
    null as obligatoria,
    null as activa,
    null as nota_aux

  from carreras
union all
select
    carreras.nro_carrera,
    carreras.nombre as nombre_carrera,
    carreras.nombre_largo as nombre_largo_carrera,
    carreras.nombre_administrativo as nombre_administrativo_carrera,
    carreras.administrativo,
    carreras.condicional,
    carreras.inhabilitado as inhabilitado_carrera,


    planes.nro_plan,
    planes.nombre as nombre_plan,
    planes.duracion_cursada,
    planes.rendidas_cantidad,
    planes.nota_aprobacion,
    planes.nota_promocion,
    planes.nota_aprobacion_libre,
    planes.comisiones_rendidas,
    planes.comisiones_duracion,
    planes.inhabilitado as inhabilitado_plan,
    null as nro_materia,
    null as nombre_materia,
    null as nombre_largo,
    null as curso,
    null as explan,
    null as nota,
    null as analitico,
    null as obligatoria,
    null as activa,
    null as nota_aux

  from planes
  inner join carreras on carreras.nro_carrera = planes.nro_carrera
union all
select
    carreras.nro_carrera,
    carreras.nombre as nombre_carrera,
    carreras.nombre_largo as nombre_largo_carrera,
    carreras.nombre_administrativo as nombre_administrativo_carrera,
    carreras.administrativo,
    carreras.condicional,
    carreras.inhabilitado as inhabilitado_carrera,


    planes.nro_plan as nro_plan,
    planes.nombre as nombre_plan,
    null as duracion_cursada,
    null as rendidas_cantidad,
    null as nota_aprobacion,
    null as nota_promocion,
    null as nota_aprobacion_libre,
    null as comisiones_rendidas,
    null as comisiones_duracion,
    null as inhabilitado_plan,


    materias.nro_materia,
    materias.nombre as nombre_materia,
    materias.nombre_largo,
    materias.curso,
    materias.explan,
    materias.nota,
    materias.analitico,
    materias.obligatoria,
    materias.activa,
    materias.nota_aux

  from materias
  inner join planes on planes.nro_plan = materias.nro_plan
  inner join carreras on carreras.nro_carrera = planes.nro_carrera
) alias_carreras
where
  (        
    @activo = -1 or
    (@activo = 0 and alias_carreras.inhabilitado_carrera) or
    (@activo = 1 and not alias_carreras.inhabilitado_carrera)
  ) and
  (nombre_carrera || nombre_largo_carrera || nombre_administrativo_carrera containing @nombre_carrera)
order by
  2 desc,
  1 desc,
  9 desc,
  8 desc,
  19 desc,
  18 desc