| Author |
 Topic  |
|
|
masond
Posting Yak Master
241 Posts |
 Posted - 10/12/2012 : 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
United Kingdom
3560 Posts |
Posted - 10/12/2012 : 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
Posting Yak Master
241 Posts |
Posted - 10/12/2012 : 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
United Kingdom
3560 Posts |
Posted - 10/15/2012 : 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
Posting Yak Master
241 Posts |
Posted - 10/15/2012 : 09:34:03
|
Thank you very much rickD
You are a * |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47147 Posts |
Posted - 10/15/2012 : 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/
|
|
|
| |
Topic |
|
|
|
Union all Help
