I have created a query to send mail in HTML format, with tables.
Then I had to add another query to my query using the union statement.
There I ran into the problem that the FOR XML clause cannot be used with union.
Following the suggestion of other users, I tried to wrap my query in the following way (unionselect):
DECLARE @cuerpo NVARCHAR(max)
DECLARE @tuplas int
DECLARE @profile char(20)
DECLARE @lista_distribucion char(200)
DECLARE @control char(300)
DECLARE @separador char(1) = CHAR(9)
DECLARE @query2 varchar(2048)
begin
execute as login = 'sige_java'
set @profile='SIGE'
set @lista_distribucion='[email protected]'
set @control='ASDASDASD'
SET @Cuerpo = N'<style type="text/css">
h2,
body {
font-family: Arial, sans-serif;
}
table {
margin: 0 auto;
border-collapse: collapse;
}
table td {
padding: 6px;
border: 3px solid white;
background-color:#ffffff;
color:#000000;
font-size:11px;
text-align: center;
}
table th {
padding: 6px;
border: 3px solid white;
background-color:#cc0000;
color:#ffffff;
font-size:10px;
font-weight: bold;
}
</style>'
+ N'<table border="1">' + N'<tr>
<th>Fecha de inicio</th>
<th>Producto</th>
<th>OBS</th>
<th>Precio U$S</th>
<th>Precio $</th>
<th>Nombre</th>' +
CAST (
(
SELECT ( -- WRAP QUERY UNION
SELECT
TD = cast(a.FAPromocionFchIni as date), '',
TD = b.FAPromocionPrdId, '',
TD = 'Ingreso Oferta Pesos $', '',
TD = '----', '',
TD = CONVERT(varchar,b.FAPromocionPrecio*1.22,103), '',
TD = c.PrdDsc, ''
from [FIVISA].[dbo].FAPROMOCIONES a
join [FIVISA].[dbo].FAPROMOCIONESPRODUCTOS b on a.FAPromocionId=b.FAPromocionId and b.FAPromocionPrdActivo=1
join [FIVISA].[dbo].PRODUC c on b.FAPromocionPrdId=c.PrdId
where a.FaPromocionEstado='ING' and a.FAPromocionMonId=0000
and FAPromocionFchIni between dateadd(day,-7,GETDATE()) and GETDATE()
UNION
SELECT
TD = cast(a.FAPromocionFchIni as date), '',
TD = b.FAPromocionPrdId, '',
TD = 'Cambio Precio Oferta', '',
TD = '----', '',
TD = '----', '',
TD = c.PrdDsc, ''
from [FIVISA].[dbo].FAPROMOCIONES a
join [FIVISA].[dbo].FAPROMOCIONESPRODUCTOS b on a.FAPromocionId=b.FAPromocionId and b.FAPromocionPrdActivo=1
join [FIVISA].[dbo].PRODUC c on b.FAPromocionPrdId=c.PrdId
where a.FAPromocionFchFin between dateadd(day,-7,GETDATE()) and GETDATE()
) as unionselect
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)
) + '</b>' +
N'</table>';
----ENVIO DEL EMAIL
EXEC msdb.dbo.sp_send_dbmail
@recipients = @lista_distribucion,
@subject = @control,
@body = @Cuerpo,
@body_format = 'HTML',
@profile_name = @profile;
end;
The error that returns is:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Am I wrapping it correctly?
I was able to solve as follows:
DECLARE @cuerpo NVARCHAR(max)
DECLARE @tuplas int
DECLARE @profile char(20)
DECLARE @lista_distribucion char(500)
DECLARE @control char(300)
DECLARE @separador char(1) = CHAR(9)
DECLARE @query2 varchar(2048)
begin
execute as login = 'sige_java'
set @profile ='SIGE'
set @lista_distribucion = '[email protected]'
set @control = 'FIVISA: Actualizaciones de productos en promoción'
SET @cuerpo = N'<style type="text/css">
h2,
body {
font-family: Arial, sans-serif;
}
table {
margin: 0 auto;
border-collapse: collapse;
}
table td {
padding: 6px;
border: 3px solid white;
background-color:#ffffff;
color:#000000;
font-size:11px;
text-align: center;
}
table th {
padding: 6px;
border: 3px solid white;
background-color:#cc0000;
color:#ffffff;
font-size:10px;
font-weight: bold;
}
</style>'
+ N'<table border="1">'
+ N'<tr>
<th>Fecha de inicio</th>
<th>Producto</th>
<th>OBS</th>
<th>Precio U$S</th>
<th>Precio $</th>
<th>Nombre</th>' +
CAST((
SELECT * from (
SELECT
(select cast(a.FAPromocionFchIni as date) as 'td' for xml path(''), type) as 'Pepe1',
(select b.FAPromocionPrdId as 'td' for xml path(''), type) as 'Pepe2',
(select 'Ingreso Oferta dólares U$S' as 'td' for xml path(''), type) as 'Pepe3',
(select CONVERT(varchar,b.FAPromocionPrecio*1.22,103) as 'td' for xml path(''), type) as 'Pepe4',
(select '----' as 'td' for xml path(''), type) as 'Pepe5',
(select c.PrdDsc as 'td' for xml path(''), type) as 'Pepe6'
from [FIVISA].[dbo].FAPROMOCIONES a (nolock)
join [FIVISA].[dbo].FAPROMOCIONESPRODUCTOS b (nolock) on a.FAPromocionId=b.FAPromocionId and b.FAPromocionPrdActivo=1
join [FIVISA].[dbo].PRODUC c (nolock) on b.FAPromocionPrdId=c.PrdId
where a.FaPromocionEstado='ING' and a.FAPromocionMonId=2222
and FAPromocionFchIni between dateadd(day,-7,GETDATE()) and GETDATE()
UNION ALL
SELECT
(select cast(a.FAPromocionFchIni as date) as 'td' for xml path(''), type) as 'Pepe7',
(select b.FAPromocionPrdId as 'td' for xml path(''), type) as 'Pepe8',
(select 'Ingreso Oferta Pesos $' as 'td' for xml path(''), type) as 'Pepe9',
(select '----' as 'td' for xml path(''), type) as 'Pepe10',
(select CONVERT(varchar,b.FAPromocionPrecio*1.22,103) as 'td' for xml path(''), type) as 'Pepe11',
(select c.PrdDsc as 'td' for xml path(''), type) as 'Pepe12'
from [FIVISA].[dbo].FAPROMOCIONES a (nolock)
join [FIVISA].[dbo].FAPROMOCIONESPRODUCTOS b (nolock) on a.FAPromocionId=b.FAPromocionId and b.FAPromocionPrdActivo=1
join [FIVISA].[dbo].PRODUC c (nolock) on b.FAPromocionPrdId=c.PrdId
where a.FaPromocionEstado='ING' and a.FAPromocionMonId=0000
and FAPromocionFchIni between dateadd(day,-7,GETDATE()) and GETDATE()
) as K
order by cast(k.Pepe3 as nvarchar(max)) desc, cast(k.Pepe2 as nvarchar(max))
FOR XML path('tr'), type) AS NVARCHAR(MAX)) + '</b>' + N'</table>';
----ENVIO DEL EMAIL
EXEC msdb.dbo.sp_send_dbmail
@recipients = @lista_distribucion,
@copy_recipients ='[email protected];[email protected];[email protected]',
@subject = @control,
@body = @Cuerpo,
@body_format = 'HTML',
@profile_name = @profile;
end;