Union all Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Union all Help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-12 : 10:28:01

Hey Guys
I need some help

I have done a union all, however i am getting the following errors

Msg 205, Level 16, State 1, Line 14
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Any ideas ?

/****** Script f SelectTopNRows command from SSMS ******/

Declare @date smalldatetime
set @Date = (select dateadd(MM,-2,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_Histy)

Declare @dateend smalldatetime
set @dateend = DATEADD(MM,+3,@date) - 1
select @dateend

-- total number of merchants --
SELECT
distinct COUNT (Dim_Outlet.ParentID)as 'Total number of Merchants'
FROM stg_FDMS_Card_Entitlements INNER JOIN
Dim_Outlet ON stg_FDMS_Card_Entitlements.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
WHERE (stg_FDMS_Card_Entitlements.[Product Code] in ('75','78'))
and Dim_Outlet.Account_Status = '16'

union all

------Merchants added this quarter--
SELECT
distinct COUNT (Dim_Outlet.ParentID)as 'Merchants added this quarter'
FROM stg_FDMS_Card_Entitlements INNER JOIN
Dim_Outlet ON stg_FDMS_Card_Entitlements.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
WHERE (stg_FDMS_Card_Entitlements.[Product Code] in ('75','78'))
and Dim_Outlet.Account_Status = '16'
and Open_Date between @date and @dateend

union all

------Merchants lost this quarter--
SELECT
distinct COUNT (Dim_Outlet.ParentID)as 'Merchants lost this quarter'
FROM stg_FDMS_Card_Entitlements
INNER JOIN Dim_Outlet ON stg_FDMS_Card_Entitlements.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
WHERE (stg_FDMS_Card_Entitlements.[Product Code] in ('75','78'))
and Dim_Outlet.Account_Status <> '16'
and Cancel_Date between @date and @dateend

union all

SELECT 'Total Retail Locations',
Count(Dim_Outlet.FDMSAccountNo)AS [Total Retail Locations]
FROM Dim_Outlet INNER JOIN
stg_FDMS_Card_Entitlements ON Dim_Outlet.FDMSAccountNo = stg_FDMS_Card_Entitlements.FDMSAccountNo
INNER JOIN Dim_MCC ON stg_FDMS_Card_Entitlements.MCCCode = Dim_MCC.MCC
where stg_FDMS_Card_Entitlements.[Product Code] ='78'
AND dbo.[Dim_Outlet].[Account_Status]='16'
AND dim_outlet.MCC_Code in ('5013'
,'5021'
,'5045'
,'5094'
,'5172'
,'5310'
,'5311'
,'5411'
,'5422'
,'5441'
,'5451'
,'5462'
,'5499'
,'5531'
,'5533'
,'5541'
,'5542'
,'5712'
,'5734'
,'5944'
,'5983'
,'8011'
,'8021'
,'8031'
,'8041'
,'8042'
,'8043'
,'8049'
,'8050'
,'8062'
,'8071'
,'8099'
,'5137'
,'5139'
,'5611'
,'5621'
,'5631'
,'5641'
,'5651'
,'5661'
,'5681'
,'5691'
,'5697'
,'5698'
,'5947')

union All

SELECT 'Total Travel & Entertainment Locations',
count (Dim_Outlet.FDMSAccountNo) as [Total Travel & Entertainment Locations]
FROM Dim_Outlet INNER JOIN
stg_FDMS_Card_Entitlements ON Dim_Outlet.FDMSAccountNo = stg_FDMS_Card_Entitlements.FDMSAccountNo
INNER JOIN Dim_MCC ON stg_FDMS_Card_Entitlements.MCCCode = Dim_MCC.MCC
where stg_FDMS_Card_Entitlements.[Product Code] ='78'
AND dbo.[Dim_Outlet].[Account_Status]='16'
AND dim_outlet.MCC_Code in ('5811'
,'5812'
,'5813'
,'5814'
,'5945')
-- ((Alliance_FDMS.DBO.[FDR Desc - MCC Code Xref].[Industry Code])='AL'
--(Alliance_FDMS.DBO.[FDR Desc - MCC Code Xref].[Industry Code])='HM'))

union all

SELECT 'Total Service Locations',
Count(Dim_Outlet.FDMSAccountNo) AS [Total Service Locations]
FROM Dim_Outlet INNER JOIN
stg_FDMS_Card_Entitlements ON Dim_Outlet.FDMSAccountNo = stg_FDMS_Card_Entitlements.FDMSAccountNo
INNER JOIN Dim_MCC ON stg_FDMS_Card_Entitlements.MCCCode = Dim_MCC.MCC
WHERE stg_FDMS_Card_Entitlements.[Product Code] ='78'
AND dbo.[Dim_Outlet].[Account_Status]='16'
and dim_outlet.MCC_Code in ('8111'
,'8211'
,'8220'
,'8241'
,'8244'
,'8249'
,'8299'
,'8351'
,'8398'
,'8641'
,'8651'
,'8661'
,'8675'
,'8699'
,'8734'
,'8911'
,'8931'
,'8999')
--AND((Alliance_FDMS.DBO.[FDR Desc - MCC Code Xref].[Industry Code])='CS'
--(Alliance_FDMS.DBO.[FDR Desc - MCC Code Xref].[Industry Code])='AR'
--(Alliance_FDMS.DBO.[FDR Desc - MCC Code Xref].[Industry Code])='TR'
--(Alliance_FDMS.DBO.[FDR Desc - MCC Code Xref].[Industry Code])='UT'
--(Alliance_FDMS.DBO.[FDR Desc - MCC Code Xref].[Industry Code])='BS'
--(Alliance_FDMS.DBO.[FDR Desc - MCC Code Xref].[Industry Code])='SP'
--(Alliance_FDMS.DBO.[FDR Desc - MCC Code Xref].[Industry Code])='RP'
--(Alliance_FDMS.DBO.[FDR Desc - MCC Code Xref].[Industry Code])='AE'
--(Alliance_FDMS.DBO.[FDR Desc - MCC Code Xref].[Industry Code])='GS'))

union all

SELECT 'Total Ecommerce Locations',
Count(Dim_Outlet.FDMSAccountNo) AS [Total Ecommerce Locations]
FROM stg_FDMS_Card_Entitlements
INNER JOIN Dim_Outlet ON stg_FDMS_Card_Entitlements.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
WHERE Dim_Outlet.DBA_Name Like '%www%'
AND dbo.[Dim_Outlet].[Account_Status]='16'
AND stg_FDMS_Card_Entitlements.[Product Code] ='78'

union all

SELECT 'Total Other Locations',
Count(Dim_Outlet.FDMSAccountNo) AS [Total Other Locations]
FROM Dim_Outlet INNER JOIN
stg_FDMS_Card_Entitlements ON Dim_Outlet.FDMSAccountNo = stg_FDMS_Card_Entitlements.FDMSAccountNo
INNER JOIN Dim_MCC ON stg_FDMS_Card_Entitlements.MCCCode = Dim_MCC.MCC
WHERE stg_FDMS_Card_Entitlements.[Product Code] ='78'
AND dbo.[Dim_Outlet].[Account_Status]='16'
AND dim_outlet.MCC_Code in ('5713'
,'5714'
,'5718'
,'5719'
,'5722'
,'5732'
,'5733'
,'5735'
,'5912'
,'5921'
,'5931'
,'5932'
,'5933'
,'5935'
,'5937'
,'5940'
,'5941'
,'5942'
,'5943'
,'5946'
,'5948'
,'5949'
,'5950'
,'5960'
,'5962'
,'5963'
,'5964'
,'5965'
,'5966'
,'5967'
,'5968'
,'5969'
,'5970'
,'5971'
,'5972'
,'5973'
,'5975'
,'5976'
,'5977'
,'5978'
,'5992'
,'5993'
,'5994'
,'5995'
,'5996'
,'5997'
,'5998'
,'5999'
,'2741'
,'2791'
,'2842'
,'5039'
,'5044'
,'5046'
,'5047'
,'5051'
,'5065'
,'5072'
,'5074'
,'5085'
,'5099'
,'5111'
,'5122'
,'5131'
,'5169'
,'5192'
,'5193'
,'5198'
,'5199'
,'5200'
,'5211'
,'5231'
,'5251'
,'5261'
,'5271'
,'5300'
,'5309'
,'5331'
,'5399'
,'5511'
,'5521'
,'5532'
,'5551'
,'5561'
,'5571'
,'5592'
,'5598'
,'5599'
,'5655'
,'5699'
,'3064'
,'3441'
,'3582'
,'5974'
,'6015'
,'6050'
,'6534')

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-10-12 : 10:31:31

The first 3 have a single column and then you start selecting two columns.

SELECT 'Total Retail Locations',
Count(Dim_Outlet.FDMSAccountNo)AS [Total Retail Locations]

...

I think what you want is to add the first column to the first three queries.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-12 : 10:48:59

Hi rickd

Thank you for your response

how ever i dont get what you mean ?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-10-15 : 04:02:39

SELECT
distinct 'Total number of Merchants', COUNT (Dim_Outlet.ParentID)
FROM stg_FDMS_Card_Entitlements INNER JOIN
Dim_Outlet ON stg_FDMS_Card_Entitlements.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
WHERE (stg_FDMS_Card_Entitlements.[Product Code] in ('75','78'))
and Dim_Outlet.Account_Status = '16'

union all

------Merchants added this quarter--
SELECT
distinct 'Merchants added this quarter', COUNT (Dim_Outlet.ParentID)
FROM stg_FDMS_Card_Entitlements INNER JOIN
Dim_Outlet ON stg_FDMS_Card_Entitlements.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
WHERE (stg_FDMS_Card_Entitlements.[Product Code] in ('75','78'))
and Dim_Outlet.Account_Status = '16'
and Open_Date between @date and @dateend

union all

------Merchants lost this quarter--
SELECT
distinct 'Merchants lost this quarter', COUNT (Dim_Outlet.ParentID)
FROM stg_FDMS_Card_Entitlements
INNER JOIN Dim_Outlet ON stg_FDMS_Card_Entitlements.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
WHERE (stg_FDMS_Card_Entitlements.[Product Code] in ('75','78'))
and Dim_Outlet.Account_Status <> '16'
and Cancel_Date between @date and @dateend

union all

SELECT 'Total Retail Locations',
Count(Dim_Outlet.FDMSAccountNo)AS [Total Retail Locations]
FROM Dim_Outlet INNER JOIN
stg_FDMS_Card_Entitlements ON Dim_Outlet.FDMSAccountNo = stg_FDMS_Card_Entitlements.FDMSAccountNo
INNER JOIN Dim_MCC ON stg_FDMS_Card_Entitlements.MCCCode = Dim_MCC.MCC
where stg_FDMS_Card_Entitlements.[Product Code] ='78'
AND dbo.[Dim_Outlet].[Account_Status]='16'
AND dim_outlet.MCC_Code in ('5013'
,'5021'
,'5045'
,'5094'
,'5172'

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-15 : 09:34:03

Thank you very much rickD

You are a *

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-15 : 22:51:19

you could have very well made a category table with those list of ids and with category as description. By that you dont have to write different select statement like this but you just need a join with new table on id to get category description and group on that. this will be easily scalable and maintainable as addition/removal of any id just requires a simple DML operation in your mapping table rather than every time coming and changing the select code. Also you could add/remove new grouping as you want in the mapping table. Saves a lot of maintainence effort IMO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources