Union Causing The ntext data type cannot be selected as DISTINCT because it is not comparable

Question

I have two queries (comprised of some temp tables) that i want to union together. When I comment out one (doesn't matter which) of the queries with the union out, it works. But when i introduce the union, I get The ntext data type cannot be selected as DISTINCT because it is not comparable. I don't understand why since neither of the main queries use a distinct. Heres the main select for reference:

SELECT  
    dbo.CUSTTABLE.ACCOUNTNUM AS 'Account Number',
    dbo.CUSTTABLE.NAME AS 'Customer Name',
    [DESCRIPTION] AS 'Division',
    ISNULL(dbo.USERINFO.NAME, 'OPEN') AS 'Sales Engineer',
    [STATE],
    PHONE,
    PAYMTERMID AS 'Terms',
    (CASE BLOCKED
        WHEN 0 THEN 'No'
        WHEN 2 THEN 'Financial Hold'
        WHEN 1 THEN 'System Hold'
        ELSE 'Unknown' END) AS 'Status',
    [45-60],
    [61-90],
    [91-120],
    [120+],
    #temptable2.Today,
    ISNULL(USERMEMO, '') AS 'Notes',
    ISNULL(#temptable2.Today / NULLIF((ISNULL(#salesTemp.PQ1, 0) / 90), 0),0) AS 'DSO1',
    CustTable.StatisticsGroup as 'Past Due Category'

FROM
    dbo.CUSTTABLE INNER JOIN 
    dbo.DIMENSIONS ON DIMENSION2_ = NUM INNER JOIN
    #temptable2 ON dbo.CUSTTABLE.ACCOUNTNUM = #temptable2.ACCOUNTNUM LEFT OUTER JOIN
    #temptable ON dbo.CUSTTABLE.ACCOUNTNUM = #temptable.ACCOUNTNUM LEFT OUTER JOIN
    dbo.SMMSALESUNITMEMBERS ON SALESGROUP = SALESUNITID AND SALESMANAGER = 1 LEFT OUTER JOIN
    dbo.USERINFO ON ID = SALESMANID AND [ENABLE] = 1    
    JOIN #salesTemp on #salesTemp.ACCOUNTNUM = CUSTTABLE.ACCOUNTNUM
WHERE
    DIMENSION2_ IN (@division)

UNION
SELECT 
    Customer_ID AS 'Account Number',
    Customer_Name AS 'Customer Name',
    'South Bend' AS Division,
    '' AS 'Sales Engineer',
    'Indiana' AS 'State',
    '' AS 'Phone',
    Customer_Terms AS 'Terms',
    '' AS 'Status',
    [45-60],
    [61-90],
    [91-120],
    [120+],
    [Today],
    '' AS 'Notes',
    '' AS 'DSO1',
    '' AS 'Past Due Category'
FROM #temptable4

Answer 1

Change: '' AS 'DSO1', to NULL as 'DS01',

Empty set can't be converted to numeric in SQL server; which is required based on the datatype defined in the top select of the union.

I assume this is the problem because the top select in the union will return a numeric value. Since empty set can't be cast to a number, either provide a number (0) or use null.

Keep in mind when executing a union the number of columns must match and their data types must match. If they don't you get errors such as this.

As to the comment, "I don't understand why since neither of the main queries use a distinct" A UNION executes a distinct on the resulting unioned results. UNION ALL will not. This is why execution of a UNION ALL performs faster, as it doens't have to do the duplicate removal contained within the execution of a UNION.