Tom Tom - 7 months ago 9
SQL Question

UNION query fails in SQL 2014, worked in 2008

When I run the following query in SQL 2014 it fails with:

ORDER BY items must appear in the select list if the statement
contains a UNION, INTERSECT or EXCEPT operator.


Worked without issue in SQL 2008, what am I missing here?

SELECT DISTINCT co.Region,
co.CountryCode,
co.CountryName,
a.CUSTNMBR AS CustomerNo,
r.SHRTNAME AS ShortName,
r.PYMTRMID AS PaymentTerms,
a.CUSTBLNC AS TotalBalance,
a.AGPERAMT_1 AS CurrentBalance,
a.AGPERAMT_2 AS Balance15Days,
a.AGPERAMT_3 AS Balance30Days,
a.AGPERAMT_4 AS Balance60Days,
a.AGPERAMT_5 AS Balance90Days,
a.AGPERAMT_6 AS Balance120Days,
a.AGPERAMT_7 AS BalanceOver120Days
FROM GREAT_PLAINS.ANSAC.dbo.RM00103 a
JOIN GREAT_PLAINS.ANSAC.dbo.RM00101 r
ON a.CUSTNMBR = r.CUSTNMBR
JOIN Customers c
ON r.CUSTNMBR = c.CustomerNo
LEFT JOIN adminCountries co
ON c.CountryCode = co.CountryCode
WHERE a.CUSTBLNC <> 0

UNION ALL

SELECT CASE r.SALSTERR
WHEN N'ASIA' THEN N'Asia'
WHEN N'LATIN AMERICA' THEN N'Latin America'
ELSE N'ROW' END AS Region,
N'NX' AS CountryCode,
N'National Accounts',
a.CUSTNMBR AS CustomerNo,
r.CUSTNAME AS ShortName,
r.PYMTRMID AS PaymentTerms,
a.CUSTBLNC,
a.AGPERAMT_1,
a.AGPERAMT_2,
a.AGPERAMT_3,
a.AGPERAMT_4,
a.AGPERAMT_5,
a.AGPERAMT_6,
a.AGPERAMT_7
FROM GREAT_PLAINS.ANSAC.dbo.RM00103 a
JOIN GREAT_PLAINS.ANSAC.dbo.RM00101 r
ON a.CUSTNMBR = r.CUSTNMBR
WHERE LEFT(a.CUSTNMBR, 3) = 'NAT' AND
a.CUSTBLNC <> 0

ORDER BY co.Region,
co.CountryCode,
c.CustomerNo

Answer

Try to use parenthesis on both queries, between UNION ALL but leaving ORDER BY outside:

(SELECT DISTINCT co.Region, 
co.CountryCode, 
co.CountryName, 
a.CUSTNMBR AS CustomerNo, 
r.SHRTNAME AS ShortName, 
r.PYMTRMID AS PaymentTerms, 
a.CUSTBLNC AS TotalBalance, 
a.AGPERAMT_1 AS CurrentBalance, 
a.AGPERAMT_2 AS Balance15Days, 
a.AGPERAMT_3 AS Balance30Days, 
a.AGPERAMT_4 AS Balance60Days, 
a.AGPERAMT_5 AS Balance90Days, 
a.AGPERAMT_6 AS Balance120Days, 
a.AGPERAMT_7 AS BalanceOver120Days 
FROM GREAT_PLAINS.ANSAC.dbo.RM00103 a 
JOIN GREAT_PLAINS.ANSAC.dbo.RM00101 r 
ON a.CUSTNMBR = r.CUSTNMBR 
JOIN Customers c 
ON r.CUSTNMBR = c.CustomerNo 
LEFT JOIN adminCountries co 
ON c.CountryCode = co.CountryCode 
WHERE a.CUSTBLNC <> 0)

UNION ALL 

(SELECT CASE r.SALSTERR 
 WHEN N'ASIA' THEN N'Asia' 
 WHEN N'LATIN AMERICA' THEN N'Latin America' 
 ELSE N'ROW' END AS Region, 
 N'NX' AS CountryCode, 
 N'National Accounts', 
 a.CUSTNMBR AS CustomerNo, 
 r.CUSTNAME AS ShortName, 
 r.PYMTRMID AS PaymentTerms, 
 a.CUSTBLNC, 
 a.AGPERAMT_1, 
 a.AGPERAMT_2, 
 a.AGPERAMT_3, 
 a.AGPERAMT_4, 
 a.AGPERAMT_5, 
 a.AGPERAMT_6, 
 a.AGPERAMT_7 
 FROM GREAT_PLAINS.ANSAC.dbo.RM00103 a 
 JOIN GREAT_PLAINS.ANSAC.dbo.RM00101 r 
 ON a.CUSTNMBR = r.CUSTNMBR 
 WHERE LEFT(a.CUSTNMBR, 3) = 'NAT' AND
 a.CUSTBLNC <> 0 )

 ORDER BY Region, 
 CountryCode, 
 CustomerNo;

Also remove alias from ORDER BY.

Comments