none
Mejorar Vista con UNION RRS feed

  • Pregunta

  • Se tiene una Vista que trabaja con el UNION de dos tablas (son iguales en nombre, tipo y número de campos).

    Se tiene una tabla principal, que guarda los registros con fecha dentro de los últimos 15 días:

     [HN_Ondata].[dbo].[ODCalls]

    Las filas con fecha 15 días hacia atrás, se mueven las filas de la principal a la tabla "backup" , del año en cuestión.

     [HN_Ondata_BACKUP].[dbo].[ODCalls_2021]

    Por eso, se hace un UNION entre las dos tablas.

    En 2022, la vista fallará, pues habrá que hacerlo con la tabla:

     [HN_Ondata_BACKUP].[dbo].[ODCalls_2022]

    El número de campos se repite en cada UNION, y prácticamente la relación entre tablas,  y el WHERE.
    El único cambio es el uso de la tabla ODCalls por año.


    Se puede aplicar SQL Dinámico ?


    ALTER VIEW [dbo].[vw_TipologiaLlamadasv2]
    AS
    SELECT TOP (100) PERCENT   ar.IndiceEntrante AS Indice, ...

    FROM            dbo.Archivo_Cliente AS ar 
    LEFT OUTER JOIN dbo.Tipos AS t ON ar.Estado = t.Indice 
    LEFT OUTER JOIN dbo.SubTipos AS st ON ar.Detalle = st.Indice
    LEFT JOIN [dbo].[C2_AR_LLAM_BUS_SAL] ON ar.IndiceEntrante = [dbo].[C2_AR_LLAM_BUS_SAL].INDICE
    INNER JOIN [HN_Ondata].[dbo].[ODCalls] c ON ar.IndiceEntrante = c.Indice AND c.[FirstCampaign] IN ('BUS_SAL', '9599') 
    WHERE        (ar.Estado IS NOT NULL) AND (ar.Fecha IS NOT NULL)

    UNION

    SELECT TOP (100) PERCENT    ar.IndiceEntrante AS Indice, ...

    FROM            dbo.Archivo_Cliente AS ar 
    LEFT OUTER JOIN dbo.Tipos AS t ON ar.Estado = t.Indice 
    LEFT OUTER JOIN dbo.SubTipos AS st ON ar.Detalle = st.Indice
    LEFT JOIN [dbo].[C2_AR_LLAM_BUS_SAL] ON ar.IndiceEntrante = [dbo].[C2_AR_LLAM_BUS_SAL].INDICE
    INNER JOIN [HN_Ondata_BACKUP].[dbo].[ODCalls_2021] c2021 ON ar.IndiceEntrante = c2021.Indice AND c2021.[FirstCampaign] IN ('BUS_SAL', '9599') 
    WHERE        (ar.Estado IS NOT NULL) AND (ar.Fecha IS NOT NULL)


    --FROM            dbo.Archivo_Cliente AS ar 
    --LEFT OUTER JOIN dbo.Tipos AS t ON ar.Estado = t.Indice 
    --LEFT OUTER JOIN dbo.SubTipos AS st ON ar.Detalle = st.Indice
    --LEFT JOIN [dbo].[C2_AR_LLAM_BUS_SAL] ON ar.IndiceEntrante = [dbo].[C2_AR_LLAM_BUS_SAL].INDICE
    --INNER JOIN [HN_Ondata_BACKUP].[dbo].[ODCalls_2020] c2020 ON ar.IndiceEntrante = c2020.Indice AND c2020.[FirstCampaign] IN ('BUS_SAL', '9599') 
    --WHERE        (ar.Estado IS NOT NULL) AND (ar.Fecha IS NOT NULL)

    order by FechaLlamada desc, id
    GO

    martes, 13 de abril de 2021 7:30

Respuestas

  • Hola Miriam Pasion:

    Si desde un procedure no tienes ningún problema, ahi si puedes usar el tsql dinámico.

    Create procedure dbo.miProc (@year int)
    as
    begin
    
    Declare @dbName nvarchar(128);
    Set @dbName = CONCAT(N'[HN_Ondata_BACKUP].[dbo].[ODCalls_',CAST(@year as nvarchar(4)));
    
    Declare @query nvarchar(max);
    Set @query = N'
    
    Select * from (
    SELECT ar.IndiceEntrante AS Indice, *
    
    FROM            dbo.Archivo_Cliente AS ar 
    LEFT OUTER JOIN dbo.Tipos AS t ON ar.Estado = t.Indice 
    LEFT OUTER JOIN dbo.SubTipos AS st ON ar.Detalle = st.Indice
    LEFT JOIN [dbo].[C2_AR_LLAM_BUS_SAL] ON ar.IndiceEntrante = [dbo].[C2_AR_LLAM_BUS_SAL].INDICE
    INNER JOIN [HN_Ondata].[dbo].[ODCalls] c ON ar.IndiceEntrante = c.Indice AND c.[FirstCampaign] IN (''BUS_SAL'', ''9599'') 
    WHERE        (ar.Estado IS NOT NULL) AND (ar.Fecha IS NOT NULL)
    
    
    UNION
    
    SELECT  ar.IndiceEntrante AS Indice, *
    
    FROM            dbo.Archivo_Cliente AS ar 
    LEFT OUTER JOIN dbo.Tipos AS t ON ar.Estado = t.Indice 
    LEFT OUTER JOIN dbo.SubTipos AS st ON ar.Detalle = st.Indice
    LEFT JOIN [dbo].[C2_AR_LLAM_BUS_SAL] ON ar.IndiceEntrante = [dbo].[C2_AR_LLAM_BUS_SAL].INDICE
    INNER JOIN ' + @dbName + N' dfw ON ar.IndiceEntrante = dfw.Indice AND dfw.[FirstCampaign] IN (''BUS_SAL'', ''9599'') 
    WHERE        (ar.Estado IS NOT NULL) AND (ar.Fecha IS NOT NULL)
    ) as d
    order by FechaLlamada desc, id;';
    
    exec sp_ExecuteSql @query;
    END;
    GO

    Te he puesto un ejemplo similar a lo que necesitas.

    Cosas a tener en cuenta, que yo he puesto el año como parámetro, y a lo mejor no te es necesario, porque si depende de una fecha podrías hacerlo directamente en el procedure.

    Luego como no se las columnas he dejado el select ...as Indice y he puesto un * (recomendable poner las columnas por su nombre real, sobre todo en el primer conjunto.

    Toda la query la he metido dentro de una tabla derivada llamada d que es la que hace el order by.

    Al final tenemos una query dinámica en una variable, que es la que se ejecuta.

    Antes de ejecutar el procedure, cambia el exec por un print @query y así con lo que te devuelva en la ventana de mensajes, lo pegas en una ventana de consultas y el intellisense te ayudará con las sintaxis incorrecta (si la hay).

    Te dejo unos enlaces, que te pueden ayudar.

    Sql dinámico

    https://javifer2.wordpress.com/2021/02/03/sql-dinamico-como-se-hace/

    Union

    https://javifer2.wordpress.com/2020/04/07/union-vs-union-all/

    Tablas derivadas

    https://javifer2.wordpress.com/2019/11/06/tabla-derivada/

    • Marcado como respuesta Miriam Pasion martes, 20 de abril de 2021 12:52
    martes, 13 de abril de 2021 14:23

Todas las respuestas

  • Hola Miriam Pasion:

    Entiendo que la pregunta es si se puede aplicar SQL dínamico en una vista.

    La respuesta es NO

    El motor te denegará la creación o modificación de cualquier vista que incluya una llamada a algo que requiera parámetros, o que contenga un exec o que pueda modificar la estructura de los objetos.

    Eso solo lo puedes hacer con un procedure. 

    Además esa sintaxis de la vista, puede funcionar, pero nada garantiza que una vista te devuelva los resultados ordenados aun cuando utilices top (100) percent y order by

    Ya que el motor devolverá los registros de la manera más eficiente, que considere, si esto encaja en el order by, genial, sino no los devovlerá sin ordenar.

    https://stackoverflow.com/questions/56528598/is-it-possible-to-use-sp-executesql-to-create-a-view

    https://stackoverflow.com/questions/15187676/create-a-view-with-order-by-clause

    Create view dbo.topOrderByIncorrecto 
    as
    Select  top(100) PERCENt *
    from
    (select *
    	from (values(1),(3),(2))t(n)
    	union all
    select *
    	from (values(6),(4),(5))t(n)
    )d
    order by d.n
    GO

    ´Sin embargo la salida es:

    Este es un simple ejemplo de que order by puede no funcionar en las vistas.

    martes, 13 de abril de 2021 8:31
  • La vista se utiliza desde un Excel, para mostrar los resultados en una Hoja.

    Sería posible utilizar un procedimiento almacenado, en lugar de la vista, con la opción de SQL dinámico ?

    martes, 13 de abril de 2021 13:48
  • Hola Miriam Pasion:

    Si desde un procedure no tienes ningún problema, ahi si puedes usar el tsql dinámico.

    Create procedure dbo.miProc (@year int)
    as
    begin
    
    Declare @dbName nvarchar(128);
    Set @dbName = CONCAT(N'[HN_Ondata_BACKUP].[dbo].[ODCalls_',CAST(@year as nvarchar(4)));
    
    Declare @query nvarchar(max);
    Set @query = N'
    
    Select * from (
    SELECT ar.IndiceEntrante AS Indice, *
    
    FROM            dbo.Archivo_Cliente AS ar 
    LEFT OUTER JOIN dbo.Tipos AS t ON ar.Estado = t.Indice 
    LEFT OUTER JOIN dbo.SubTipos AS st ON ar.Detalle = st.Indice
    LEFT JOIN [dbo].[C2_AR_LLAM_BUS_SAL] ON ar.IndiceEntrante = [dbo].[C2_AR_LLAM_BUS_SAL].INDICE
    INNER JOIN [HN_Ondata].[dbo].[ODCalls] c ON ar.IndiceEntrante = c.Indice AND c.[FirstCampaign] IN (''BUS_SAL'', ''9599'') 
    WHERE        (ar.Estado IS NOT NULL) AND (ar.Fecha IS NOT NULL)
    
    
    UNION
    
    SELECT  ar.IndiceEntrante AS Indice, *
    
    FROM            dbo.Archivo_Cliente AS ar 
    LEFT OUTER JOIN dbo.Tipos AS t ON ar.Estado = t.Indice 
    LEFT OUTER JOIN dbo.SubTipos AS st ON ar.Detalle = st.Indice
    LEFT JOIN [dbo].[C2_AR_LLAM_BUS_SAL] ON ar.IndiceEntrante = [dbo].[C2_AR_LLAM_BUS_SAL].INDICE
    INNER JOIN ' + @dbName + N' dfw ON ar.IndiceEntrante = dfw.Indice AND dfw.[FirstCampaign] IN (''BUS_SAL'', ''9599'') 
    WHERE        (ar.Estado IS NOT NULL) AND (ar.Fecha IS NOT NULL)
    ) as d
    order by FechaLlamada desc, id;';
    
    exec sp_ExecuteSql @query;
    END;
    GO

    Te he puesto un ejemplo similar a lo que necesitas.

    Cosas a tener en cuenta, que yo he puesto el año como parámetro, y a lo mejor no te es necesario, porque si depende de una fecha podrías hacerlo directamente en el procedure.

    Luego como no se las columnas he dejado el select ...as Indice y he puesto un * (recomendable poner las columnas por su nombre real, sobre todo en el primer conjunto.

    Toda la query la he metido dentro de una tabla derivada llamada d que es la que hace el order by.

    Al final tenemos una query dinámica en una variable, que es la que se ejecuta.

    Antes de ejecutar el procedure, cambia el exec por un print @query y así con lo que te devuelva en la ventana de mensajes, lo pegas en una ventana de consultas y el intellisense te ayudará con las sintaxis incorrecta (si la hay).

    Te dejo unos enlaces, que te pueden ayudar.

    Sql dinámico

    https://javifer2.wordpress.com/2021/02/03/sql-dinamico-como-se-hace/

    Union

    https://javifer2.wordpress.com/2020/04/07/union-vs-union-all/

    Tablas derivadas

    https://javifer2.wordpress.com/2019/11/06/tabla-derivada/

    • Marcado como respuesta Miriam Pasion martes, 20 de abril de 2021 12:52
    martes, 13 de abril de 2021 14:23
  • Al ser cadenas y usarlas con sp_ExecuteSql  , se pueden definir cadenas con las partes comunes (select con campos, from y joins, where común) para no repetirse.


    sp_ExecuteSql tiene alguna desventaja ? rendimiento o seguridad?

    martes, 20 de abril de 2021 12:59
  • Hola Miriam Pasion:

    se pueden definir cadenas con las partes comunes

    Si, se puede. Esa parte no tiene ningún problema.

    sp_ExecuteSql tiene alguna desventaja? rendimiento o seguridad?

    Es un procedure muy poderoso. El rendimiento, es diferente, porque las querys dinámicas se tienen que analizar al vuelo. No obstante funciona excepcionalmente bien. Pero no es lo mismo, que algo estático. A veces el rendimiento puede llegar a ser superior incluso.

    Seguridad. Ahi si hay algo, pero depende de ti. Los paramétros para inyectar a sp_ExecuteSql, deberías de definirlos como tal. Esta parte es más engorrosa, pero sirve para evitar la inyección sql, que es el gran problema del SQL dinámico. (Su gran virtud es su gran defecto).

    sp_ExecuteSql, tiene la virtud de poder quitar el defecto de las querys dinámicas, pero su sintaxis es al menos bastante engorrosa.

    En el enlace de como se hace el Sql dinámico, tienes una sección llamada 

    sp_executeSql para evitar la inyección de SQL

    https://javifer2.wordpress.com/2021/02/03/sql-dinamico-como-se-hace/

    martes, 20 de abril de 2021 13:44