CODEWITHSUNDEEP
sqlsql-servert-sql
user3086989
user3086989

Reputation: 75

What does ")x" mean in a query?

I have the following stored procedure (in MS SQL):

SET NOCOUNT ON

DECLARE @Cuantos INT

IF EXISTS
    (
        SELECT TOP 1 * FROM IntProgramas WHERE cod_programa IN
        (   SELECT cod_programa FROM IntGrupo_programa WHERE cod_grupo IN
            (   SELECT cod_grupo FROM IntUsuarios WHERE cod_usuario = @cod_usuario
            )
        )
    )
BEGIN
    SET nocount ON

            SELECT P.cod_programa
                     ,nb_programa
                     ,descripcion  
                     ,secuencia
                     ,P.Accion
                     ,P.Controlador
                     INTO #mitabla1 
            FROM IntProgramas P
            WHERE P.cod_programa    
            IN (
                    SELECT cod_programa FROM Intgrupo_programa WHERE cod_grupo IN 
                    (
                    SELECT cod_grupo FROM Intusuarios WHERE cod_usuario=@cod_usuario
                    )
                ) 

            SET nocount ON

            SELECT GP.cod_programa
            ,P.nb_programa
            ,P.descripcion    
            INTO #mitabla2 
            FROM IntGrupo_Programa GP 
            JOIN Intprogramas  P on GP.cod_programa = P.cod_programa
            WHERE GP.cod_grupo IN (SELECT cod_grupo FROM Intusuarios WHERE cod_usuario=@cod_usuario
            SELECT @Cuantos = COUNT(*) 
            FROM( SELECT  nb_programa, descripcion FROM IntProgramas 
            WHERE cod_programa in (select cod_programa from #mitabla1 
            union  select cod_programa from #mitabla2))x

            /*si existe en ambas macheo*/
            SELECT  nb_programa, descripcion, P.cod_programa 
            INTO        #mitabla3 
            FROM        IntProgramas P
            WHERE  cod_programa in (SELECT cod_programa FROM #mitabla1 
            union  SELECT cod_programa FROM #mitabla2) 

            select 
                    t.nb_programa
                  , t.descripcion
                  , t.cod_programa
                  , p.secuencia 
                  , ISNULL(et.cod_menu,0) as cod_menu
                  , ISNULL(et.desc_menu,0) as desc_menu
                  , ISNULL(et_sprog.cod_sub_menu_programa,0) AS cod_sub_menu_programa
                  , ISNULL(et_sprov.desc_sub_menu,0) AS desc_sub_menu_N2
                  , p.Accion
                  , p.Controlador
            from #mitabla3 t 
            JOIN      IntProgramas p
                ON t.cod_programa = p.cod_programa

            LEFT JOIN IntEstructura_sub_menu_programa et_sprog 
                ON t.cod_programa = et_sprog.cod_programa 

            LEFT JOIN IntEstructura_menu_Usuarios et 
                ON et_sprog.cod_menu = et.cod_menu 

            LEFT JOIN IntEstructura_sub_menu_Usuarios et_sprov 
                ON et_sprog.cod_sub_menu_programa = et_sprov.cod_sub_menu

            WHERE et_sprog.cod_programa IS NOT NULL

            order by et.cod_menu, et_sprog.cod_sub_menu_programa, p.secuencia
            --gp.cod_grupo, 

            drop table #mitabla1 
            drop table #mitabla2
            drop table #mitabla3


END

i am trying to understand it but when I get to the line 

union  select cod_programa from #mitabla2))x

I don't understand what the x does any help would be apreciated. I have been trying to run the SP in parts to better understand the flow but that line has really complicated things for me.

Upvotes: 4

Views: 9343

Answers (3)

Filipe Silva
Filipe Silva

Reputation: 21657

It's an alias for the subquery:

select @Cuantos = COUNT(*)
from (
  select nb_programa, descripcion
  from IntProgramas
  where cod_programa in (
      select cod_programa
      from #mitabla1

      union

      select cod_programa
      from #mitabla2
      )
  ) x

When you do SELECT FROM (SELECT... you have to name the subquery / derived table.

It works as if you were doing: 

select @Cuantos = COUNT(*)
    from x

Unlike an alias in a table name that is used mostly to make it easier to read or to not have to repeat the table name, in this case an alias is mandatory.

Upvotes: 3

Andrew
Andrew

Reputation: 8758

X is an alias for this sub-query or derived table:

( SELECT  nb_programa, descripcion FROM IntProgramas 
            WHERE cod_programa in (select cod_programa from #mitabla1 
            union  select cod_programa from #mitabla2))

Upvotes: 5

Lloyd Banks
Lloyd Banks

Reputation: 36659

X is the alias for the results of the sub-query. There should really be a space before it though

Upvotes: 2

Related Questions