SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Union all Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 10/12/2012 :  10:28:01  Show Profile  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 10/12/2012 :  10:31:31  Show Profile  Reply with Quote
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.
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 10/12/2012 :  10:48:59  Show Profile  Reply with Quote
Hi rickd

Thank you for your response

how ever i dont get what you mean ?
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 10/15/2012 :  04:02:39  Show Profile  Reply with Quote
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'
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 10/15/2012 :  09:34:03  Show Profile  Reply with Quote
Thank you very much rickD

You are a *
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/15/2012 :  22:51:19  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000