CODEWITHSUNDEEP
sqlsql-server
GHock
GHock

Reputation: 47

Error: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists

I am trying to create a stored procedure that will email out an HTML table that uses a query with Unions in it. But I keep getting:

"All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."

I went through and I believe all my selects for the Unions have the same number for columns. Any one have any ideas? I am using SQL 2012.

DECLARE @DailySales varchar(max)

With Daily_Sales_CTE as (
SELECT 
TD = 'Base','',
TD = CONVERT(int,SUM(totalprice)/1000),'', 
TD = CONVERT(int,SUM(totalcost)/1000),'',
TD = CONVERT(int,SUM(totalprice-totalcost)/1000),'',
TD = CONVERT(decimal (3,3),1-((SUM(totalcost))/(SUM(totalprice)))),'',
TD = CONVERT(int, SUM(totweight)/2000)
FROM ubs_saleslookup_cost A
WHERE DATEPART(MM,A.date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,A.date) =          DATEPART(YYYY, GETDATE()) and DATEPART(DD,A.date) = DATEPART(DD, GETDATE()) and
a.cust<>'1522292' and a.cust<>'1435230' and a.location<>'g-ds'
union
Select
TD = 'Direct','',
TD = CONVERT(int,SUM(totalprice)/1000),'', 
TD = CONVERT(int,SUM(totalcost)/1000),'',
TD = CONVERT(int,SUM(totalprice-totalcost)/1000),'',
TD = CONVERT(decimal (3,3),1-((SUM(totalcost))/(SUM(totalprice)))),'',
TD = CONVERT(int, SUM(totweight)/2000)
FROM ubs_saleslookup_cost A
WHERE DATEPART(MM,A.date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,A.date) = DATEPART(YYYY, GETDATE()) and DATEPART(DD,A.date) = DATEPART(DD, GETDATE()) and
a.cust<>'1522292' and a.cust<>'1435230' and a.location='g-ds'
union
SELECT 
TD = 'Acero',
TD = CONVERT(int,SUM(totalprice)/b.xchgrate/1000),'', 
TD = CONVERT(int,SUM(totalcost)/b.xchgrate/1000),'',
TD = CONVERT(int,SUM(totalprice-totalcost)/b.xchgrate/1000),'',
TD = CONVERT(decimal (3,3),1-((SUM(totalcost)/b.xchgrate)/(SUM(totalprice)/b.xchgrate))),'',
TD = CONVERT(int, SUM(totweight)/2000)
FROM acero.dbo.ubs_saleslookup_cost A
left join acero.dbo.acero_rate b on DATEPART(YYYY,A.date)=b.year1 and DATEPART(MM,A.date)=b.periodid
WHERE DATEPART(MM,A.date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,A.date) = DATEPART(YYYY, GETDATE())  and   DATEPART(DD,A.date) = DATEPART(DD, GETDATE()) and
a.cust<>'1512098'
group by b.xchgrate
union
SELECT
TD = 'Chile','',
TD = CONVERT(int,SUM(totalprice)/b.xchgrate/1000),'', 
TD = CONVERT(int,SUM(totalcost)/b.xchgrate/1000),'',
TD = CONVERT(int,SUM(totalprice-totalcost)/b.xchgrate/1000),'',
TD = CONVERT(decimal (3,3),1-((SUM(totalcost)/b.xchgrate)/(SUM(totalprice)/b.xchgrate))),'',
TD = CONVERT(int, SUM(totweight)/2000)
FROM chile.dbo.ubs_saleslookup_cost A
left join chile.dbo.chile_rate b on DATEPART(YYYY,A.date)=b.year1 and DATEPART(MM,A.date)=b.periodid
WHERE DATEPART(MM,A.date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,A.date) = DATEPART(YYYY, GETDATE())  and   DATEPART(DD,A.date) = DATEPART(DD, GETDATE()) and
a.cust<>'1512098'
group by b.xchgrate
union
SELECT 
TD = 'Total','',
TD = CONVERT(int,SUM(a.totalprice+b.totalprice+isnull(c.totalprice,0))/1000),'', 
TD = CONVERT(int,SUM(a.totalcost+b.totalcost+isnull(c.totalprice,0))/1000),'',
TD = CONVERT(int,SUM(a.totalprice-a.totalcost+b.totalprice-b.totalcost+isnull(c.totalprice-c.totalcost,0))/1000),'',
TD = CONVERT(decimal (3,3),1-((SUM(a.totalcost+b.totalcost+isnull(c.totalcost,0)))/(SUM(a.totalprice+b.totalprice+isnull(c.totalprice,0))))),'',
TD = CONVERT(int, SUM(a.totweight+b.totweight+isnull(c.totweight,0)))
FROM (select 'CurMonth' as Month, sum(totalprice) as totalprice, sum(totalcost) as totalcost, sum(totweight)/2000 as totweight
from ubs_saleslookup_cost 
WHERE DATEPART(MM,date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,date) = DATEPART(YYYY, GETDATE()) and   DATEPART(DD,date) = DATEPART(DD, GETDATE()) and
cust<>'1522292' and cust<>'1435230') a
left join (select 'CurMonth' as Month, sum(a.totalprice)/b.xchgrate as totalprice, sum(a.totalcost)/b.xchgrate as totalcost, 
sum(a.totweight)/2000 as totweight
from acero.dbo.ubs_saleslookup_cost A
left join acero.dbo.acero_rate b on DATEPART(YYYY,A.date)=b.year1 and DATEPART(MM,A.date)=b.periodid
WHERE DATEPART(MM,A.date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,A.date) = DATEPART(YYYY, GETDATE())  and   DATEPART(DD,A.date) = DATEPART(DD, GETDATE()) and
a.cust<>'1512098' group by b.xchgrate) b on a.month=b.month
left join (select 'CurMonth' as Month, sum(a.totalprice)/b.xchgrate as totalprice, sum(a.totalcost)/b.xchgrate as totalcost, 
sum(a.totweight)/2000 as totweight
from chile.dbo.ubs_saleslookup_cost A
left join chile.dbo.chile_rate b on DATEPART(YYYY,A.date)=b.year1 and DATEPART(MM,A.date)=b.periodid
WHERE DATEPART(MM,A.date) = DATEPART(MM, GETDATE()) AND DATEPART(YYYY,A.date) = DATEPART(YYYY, GETDATE())  and   DATEPART(DD,A.date) = DATEPART(DD, GETDATE()) and
a.cust<>'1512098' group by b.xchgrate) c on a.month=c.month

)

Select @DailySales = 
N'<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
font-size: 13px;
font-weight: Bold;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd)    { background-color:#eee; }
tr:nth-child(even)   { background-color:#fff; } 
</style>'+  
N'<H1><font color="Black">MTD Results</H1>'+
N'<table id="box-table">'+
N'<tr><font color = "Black">
<th> Type </th>
<th> Sales </th>
<th> Cost </th>
<th> Margin </th>
<th> Percentage </th>
<th> Tons </th>
</tr>'
+ CAST ( ( select * from Daily_Sales_CTE

FOR XML PATH ('tr')
) as varchar(max))
+ '</table>'

EXEC msdb.dbo.sp_send_dbmail 
@recipients = '[email protected]',
@copy_recipients = '', 
@subject = 'Daily Sales',
@body = @DailySales,
@body_format = 'html'

Upvotes: 2

Views: 39603

Answers (1)

dean
dean

Reputation: 10098

There is one less column in the 'Acero' part (as Tony commented). Should be:

SELECT 
TD = 'Acero', '',
TD = CONVERT(int,SUM(totalprice)/b.xchgrate/1000),'',

However, when you fix this, you'll face another problem: all the columns in CTE must have a column name, and when you fix even that, you'll have the error that they are all called the same, and it's not allowed.

Upvotes: 1

Related Questions