CODEWITHSUNDEEP
sqlsql-servert-sqlunionunion-all
user2891330
user2891330

Reputation: 81

T-SQL [UNION ALL] removing records from query result

Have a simple UNION ALL query marrying the results of two queries. The first query, run independently, returns 1208 records and the second 14. I would expect a properly syntaxed UNION ALL to return 1222 records but mine falls to 896.

Makes zero sense to me: 

SELECT a.WBS_ELEMENT_ID as [WBS Element],
a.WBS_ELEMENT_DESC as [WBS Element Desc],
a.UHC_INDUSTRY as [Industry],
a.UHC_SECTOR as [Sector],
a.UHC_DUNS_NUMBER as [UHC DUNS Number],
a.UHC_DUNS_NAME as [UHC DUNS Name],
a.PRIORITY_SUB_SECTOR as [Priority Sub Sector],
a.BUDGET_ALLOCATION as [Budget Allocation],
a.LAST_UPDATED_ON as [Last Updated]
FROM DimSectorPd a
WHERE a.wbs_element_id is not null

UNION ALL

SELECT ROW_NUMBER() OVER (ORDER BY a.wbs_element_desc) as [WBS Element],
a.WBS_ELEMENT_DESC as [WBS Element name],
a.UHC_INDUSTRY as [Industry],
a.UHC_SECTOR as [Sector],
a.UHC_DUNS_NUMBER  as [UHC DUNS Number],
a.UHC_DUNS_NAME as [UHC DUNS Name],
a.PRIORITY_SUB_SECTOR as [Priority Sub Sector],
a.BUDGET_ALLOCATION as [Budget Allocation],
a.LAST_UPDATED_ON as [Last Updated]
from dimsectorpd a where a.WBS_ELEMENT_ID is null

Upvotes: 3

Views: 1661

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Your queries should return all rows in the table. Unless the table changes between executions, the results from running the subqueries separately should be the same as from running them with the UNION ALL.

As a note, if you want to simplify the query, then you can do:

SELECT COALESCE(a.WBS_ELEMENT_ID,
                ROW_NUMBER() OVER (PARTITION BY wbs_element_id ORDER BY a. wbs_element_desc)
               ) as [WBS Element],
       a.WBS_ELEMENT_DESC as [WBS Element Desc],
       a.UHC_INDUSTRY as [Industry],
       a.UHC_SECTOR as [Sector],
       a.UHC_DUNS_NUMBER as [UHC DUNS Number],
       a.UHC_DUNS_NAME as [UHC DUNS Name],
       a.PRIORITY_SUB_SECTOR as [Priority Sub Sector],
       a.BUDGET_ALLOCATION as [Budget Allocation],
       a.LAST_UPDATED_ON as [Last Updated]
FROM DimSectorPd a;

Upvotes: 1

Stephen Turner
Stephen Turner

Reputation: 7314

Here is a simplified example can you see if it works on your server?

SELECT a.low AS [My ID], 
    a.name AS [My Letter]
FROM master..spt_values as a
WHERE low is not null

UNION ALL

SELECT ROW_NUMBER() OVER (ORDER BY a.name) AS [My ID],
    a.name AS [My Letter]
FROM master..spt_values as a
WHERE a.low is null

master..spt_values as 2515 rows on my system...

Upvotes: 0

FutbolFan
FutbolFan

Reputation: 13733

Obviously there is nothing wrong with your syntax, but if you want to try a different approach to getting your UNION ALL to work with ROW_NUMBER. Here it is:

;WITH q1
AS (
    SELECT a.WBS_ELEMENT_ID AS [WBS Element]
        ,a.WBS_ELEMENT_DESC AS [WBS Element Desc]
        ,a.UHC_INDUSTRY AS [Industry]
        ,a.UHC_SECTOR AS [Sector]
        ,a.UHC_DUNS_NUMBER AS [UHC DUNS Number]
        ,a.UHC_DUNS_NAME AS [UHC DUNS Name]
        ,a.PRIORITY_SUB_SECTOR AS [Priority Sub Sector]
        ,a.BUDGET_ALLOCATION AS [Budget Allocation]
        ,a.LAST_UPDATED_ON AS [Last Updated]
    FROM DimSectorPd a
    WHERE a.wbs_element_id IS NOT NULL

    UNION ALL

    SELECT b.WBS_ELEMENT_ID AS [WBS Element]   --just bring NULL values 
        ,b.WBS_ELEMENT_DESC AS [WBS Element name]
        ,b.UHC_INDUSTRY AS [Industry]
        ,b.UHC_SECTOR AS [Sector]
        ,b.UHC_DUNS_NUMBER AS [UHC DUNS Number]
        ,b.UHC_DUNS_NAME AS [UHC DUNS Name]
        ,b.PRIORITY_SUB_SECTOR AS [Priority Sub Sector]
        ,b.BUDGET_ALLOCATION AS [Budget Allocation]
        ,b.LAST_UPDATED_ON AS [Last Updated]
    FROM dimsectorpd b
    WHERE b.WBS_ELEMENT_ID IS NULL
    )
SELECT CASE 
        WHEN q1.[WBS Element] IS NULL
            THEN ROW_NUMBER() OVER (ORDER BY q1.WBS_Element_Desc)
        ELSE q1.[WBS Element]
        END [WBS_Element]
    ,q1.[WBS Element Desc]
    ,q1.Industry
    ,q1.Sector
    ,q1.[UHC DUNS Number]
    ,q1.[UHC DUNS Name]
    ,q1.[Priority Sub Sector]
    ,q1.[Budget Allocation]
    ,q1.[Last Updated]
FROM q1

Upvotes: 0

Related Questions