Union All with Following Script-CodePudding

SELECT 'Region 1' as [Entity],      
[name].[dbo].[TableName$G_L Account$437].[No_] As 'GL Account'  
,[name].[dbo].[TableName$G_L Account$437].[Name] As 'GL Name'  
,CASE [name].[dbo].[TableName$G_L Account$437].[Account Type] WHEN 0 THEN 'Posting' WHEN 1 THEN 'Heading' WHEN 2 THEN 'Total' WHEN 3 THEN 'Begin-Total' WHEN 4 THEN 'End-Total' END As 'GL Account Type'  
,CASE [name].[dbo].[TableNamee$G_L Account$437].[Income_Balance] WHEN 0 THEN 'Income Statement' WHEN 1 THEN 'Balance Sheet' End As 'Income/Balance'      
,CASE [name].[dbo].[TableName$G_L Account$271].[Exclude from Provisional] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' End As 'GL Excluded'   
FROM [name].[dbo].[TableNamee$G_L Account$437]   
JOIN [name].[dbo].[TableName$G_L Account$271] ON [name].[dbo].[TableName$G_L Account$437].[No_]=[name].[dbo].[TableName$G_L Account$271].[No_];

The above code works on it's own. But I would like to do the following:

SELECT 'Region 1' as [Entity],      
[name].[dbo].[TableName$G_L Account$437].[No_] As 'GL Account'  
,[name].[dbo].[TableName$G_L Account$437].[Name] As 'GL Name'  
,CASE [name].[dbo].[TableName$G_L Account$437].[Account Type] WHEN 0 THEN 'Posting' WHEN 1 THEN 'Heading' WHEN 2 THEN 'Total' WHEN 3 THEN 'Begin-Total' WHEN 4 THEN 'End-Total' END As 'GL Account Type'  
,CASE [name].[dbo].[TableNamee$G_L Account$437].[Income_Balance] WHEN 0 THEN 'Income Statement' WHEN 1 THEN 'Balance Sheet' End As 'Income/Balance'      
,CASE [name].[dbo].[TableName$G_L Account$271].[Exclude from Provisional] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' End As 'GL Excluded'   
FROM [name].[dbo].[TableNamee$G_L Account$437]   
JOIN [name].[dbo].[TableName$G_L Account$271] ON [name].[dbo].[TableName$G_L Account$437].[No_]=[name].[dbo].[TableName$G_L Account$271].[No_];


Union All
SELECT 'Region 2' as [Entity],      
[name].[dbo].[TableName$G_L Account$437].[No_] As 'GL Account'  
,[name].[dbo].[TableName$G_L Account$437].[Name] As 'GL Name'  
,CASE [name].[dbo].[TableName$G_L Account$437].[Account Type] WHEN 0 THEN 'Posting' WHEN 1 THEN 'Heading' WHEN 2 THEN 'Total' WHEN 3 THEN 'Begin-Total' WHEN 4 THEN 'End-Total' END As 'GL Account Type'  
,CASE [name].[dbo].[TableNamee$G_L Account$437].[Income_Balance] WHEN 0 THEN 'Income Statement' WHEN 1 THEN 'Balance Sheet' End As 'Income/Balance'      
,CASE [name].[dbo].[TableName$G_L Account$271].[Exclude from Provisional] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' End As 'GL Excluded'   
FROM [name].[dbo].[TableNamee$G_L Account$437]   
JOIN [name].[dbo].[TableName$G_L Account$271] ON [name].[dbo].[TableName$G_L Account$437].[No_]=[name].[dbo].[TableName$G_L Account$271].[No_];

Can anyone help to use the union all here as I have 30 regions to query simultaneously

CodePudding user response：

Simply needed to remove ; and it works

CodePudding user response：

UNION ALL should work IF each query returns the same number of columns with the same data types.

If this is a stored procedure or function, you may want to consider whether UNION ALL is your best option. You could create a temp table, retrieve the data from each query into that table, the select the results from the temp table.