UNION IF query problem
-
Hi,
SQL Server 2000.
I've written a view that is 2 queries joined by UNION ALL. Now my problem is, if either part of the query has no results, I want a result returned with zero values.
Using IF EXISTS I can get the 2 parts of the UNION to work exactly as I want. But when I put them in one query with UNION, I get an error that says "Incorrect syntax near the keyword 'IF'".
Is my syntax actually wrong, or is this SQL Servers way of telling I can't do what I want to do while using UNION?
Thanks
Paul
IF EXISTS
(SELECT AG.GroupMeat_Name, GLAGData.AccountGroup_ID
FROM Export_AccountGroup AS AG
INNER JOIN Export_GL_AccountGroup_Data AS GLAGData
ON AG.AccountGroup_ID = GLAGData.AccountGroup_ID
WHERE (AG.GroupMeat_Name = N'COGS'))
SELECT 'COGS' AS Type,
EV.Version_Name,
GLAGData.FISCALYEAR,
SUM(GLAGData.M1) AS M1,
SUM(GLAGData.M2 - GLAGData.M1) AS M2,
SUM(GLAGData.M3 - GLAGData.M2) AS M3,
SUM(GLAGData.M4 - GLAGData.M3) AS M4,
SUM(GLAGData.M5 - GLAGData.M4) AS M5,
SUM(GLAGData.M6 - GLAGData.M5) AS M6,
SUM(GLAGData.M7 - GLAGData.M6) AS M7,
SUM(GLAGData.M8 - GLAGData.M7) AS M8,
SUM(GLAGData.M9 - GLAGData.M8) AS M9,
SUM(GLAGData.M10 - GLAGData.M9) AS M10,
SUM(GLAGData.M11 - GLAGData.M10) AS M11,
SUM(GLAGData.M12 - GLAGData.M11) AS M12
FROM dbo.Export_AccountGroup AS AG
INNER JOIN dbo.Export_GL_AccountGroup_Data AS GLAGData
ON AG.AccountGroup_ID = GLAGData.AccountGroup_ID
INNER JOIN dbo.Export_Versions AS EV
ON GLAGData.Version_ID = EV.Version_ID
WHERE (AG.GroupMeat_Name = N'COGS')
GROUP BY EV.Version_Name, GLAGData.FISCALYEAR
ELSE
SELECT 'COGS' AS Type, 'Plan Basis' AS [Version Name], '2007' as [FiscalYear], '0' AS M1, '0' AS M2,'0' AS M3,'0' AS M4,'0' AS M5,'0' AS M6,
'0' AS M7,'0' AS M8,'0' AS M9,'0' AS M10,'0' AS M11,'0' AS M12
UNION ALL
IF EXISTS (SELECT AG.GroupMeat_Name, GLAGData.AccountGroup_ID
FROM Export_AccountGroup AS AG
INNER JOIN Export_GL_AccountGroup_Data AS GLAGData
ON AG.AccountGroup_ID = GLAGData.AccountGroup_ID
WHERE (AG.GroupMeat_Name = N'REVENUE') OR
(AG.GroupMeat_Name = N'Other Income') OR
(AG.GroupMeat_Name = N'Interest Income') OR
(AG.GroupMeat_Name = N'Gain on Disposal of Assets') OR
(AG.GroupMeat_Name = N'Returns & Allowances'))
SELECT 'Revenue' AS Type,
EV_1.Version_Name,
GLAGData_1.FISCALYEAR,
SUM(GLAGData_1.M1) * - 1 AS M1,
SUM(GLAGData_1.M2 - GLAGData_1.M1) * - 1 AS M2,
SUM(GLAGData_1.M3 - GLAGData_1.M2) * - 1 AS M3,
SUM(GLAGData_1.M4 - GLAGData_1.M3) * - 1 AS M4,
SUM(GLAGData_1.M5 - GLAGData_1.M4) * - 1 AS M5,
SUM(GLAGData_1.M6 - GLAGData_1.M5) * - 1 AS M6,
SUM(GLAGData_1.M7 - GLAGData_1.M6) * - 1 AS M7,
SUM(GLAGData_1.M8 - GLAGData_1.M7) * - 1 AS M8,
SUM(GLAGData_1.M9 - GLAGData_1.M8) * - 1 AS M9,
SUM(GLAGData_1.M10 - GLAGData_1.M9) * - 1 AS M10,
SUM(GLAGData_1.M11 - GLAGData_1.M10) * - 1 AS M11,
SUM(GLAGData_1.M12 - GLAGData_1.M11) * - 1 AS M12
FROM dbo.Export_AccountGroup AS AG_1
INNER JOIN dbo.Export_GL_AccountGroup_Data AS GLAGData_1
ON AG_1.AccountGroup_ID = GLAGData_1.AccountGroup_ID
INNER JOIN dbo.Export_Versions AS EV_1
ON GLAGData_1.Version_ID = EV_1.Version_ID
WHERE (AG_1.GroupMeat_Name = N'REVENUE') OR
(AG_1.GroupMeat_Name = N'Other Income') OR
(AG_1.GroupMeat_Name = N'Interest Income') OR
(AG_1.GroupMeat_Name = N'Gain on Disposal of Assets') OR
(AG_1.GroupMeat_Name = N'Returns & Allowances')
GROUP BY EV_1.Version_Name, GLAGData_1.FISCALYEAR
ELSE
SELECT 'Revenue' AS Type, 'Plan Basis' AS [Version Name], '2007' as [FiscalYear], '0' AS M1, '0' AS M2,'0' AS M3,'0' AS M4,'0' AS M5,'0' AS M6,
'0' AS M7,'0' AS M8,'0' AS M9,'0' AS M10,'0' AS M11,'0' AS M12
-
You need to add BEGIN and END around the blocks of code inside of your IF and ELSE statements:
IF {condition}
BEGIN
select ...
UNION ALL
END
ELSE
BEGIN
...
END
-
Thanks for the reply but that isn't how my query is structured.
My query is structured like this:
IF {condition}
Select...
Else
Select...
UNION ALL
IF {condition}
Select...
Else
Select...
Thanks!
-
DECLARE @GroupMeat TABLE ( -- Must be static table in DB!
Name nvarchar(100),
Group nvarchar(100)
)
INSERT INTO @GroupMeat (Name, Group)
SELECT N'COGS', N'COGS'
UNION
SELECT N'REVENUE', N'REVENUE'
UNION
SELECT N'Other Income', N'REVENUE'
UNION
SELECT N'Interest Income', N'REVENUE'
UNION
SELECT N'Gain on Disposal of Assets', N'REVENUE'
UNION
SELECT N'Returns & Allowances', N'REVENUE'
SELECT G.Group AS Type,
ISNULL(EV.Version_Name, 'Plan Basis'), -- Actually this is an error
ISNULL(GLAGData.FISCALYEAR, '2007'), -- Hardcoded values illustrate errors in design
ISNULL(SUM(GLAGData.M1), 0) AS M1,
..... -- same ISNULL for all other columns
FROM dbo.Export_AccountGroup AS AG
INNER JOIN @GroupMeat G ON AG.GroupMeat_Name = G.Name
LEFT JOIN dbo.Export_GL_AccountGroup_Data AS GLAGData
ON AG.AccountGroup_ID = GLAGData.AccountGroup_ID
LEFT JOIN dbo.Export_Versions AS EV
ON GLAGData.Version_ID = EV.Version_ID
GROUP BY G.Group, EV.Version_Name, GLAGData.FISCALYEAR
---------------------------
That's it.
No IF's no UNION's.
_____________
Code for TallyGenerator -
You could select each query into a temp table or temp variable saving each @@RowCount then if both row counts are not zero select both tables with Union All...
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
Give this a try, couldn't hurt:
select
Type,
Version_Name,
FISCALYEAR,
M1,
M2,
M3,
M4,
M5,
M6,
M7,
M8,
M9,
M10,
M11,
M12
from (
IF EXISTS (SELECT
AG.GroupMeat_Name,
GLAGData.AccountGroup_ID
FROM
Export_AccountGroup AS AG
INNER JOIN Export_GL_AccountGroup_Data AS GLAGData
ON (AG.AccountGroup_ID = GLAGData.AccountGroup_ID)
WHERE (AG.GroupMeat_Name = N'COGS'))
SELECT
'COGS' AS Type,
EV.Version_Name,
GLAGData.FISCALYEAR,
SUM(GLAGData.M1) AS M1,
SUM(GLAGData.M2 - GLAGData.M1) AS M2,
SUM(GLAGData.M3 - GLAGData.M2) AS M3,
SUM(GLAGData.M4 - GLAGData.M3) AS M4,
SUM(GLAGData.M5 - GLAGData.M4) AS M5,
SUM(GLAGData.M6 - GLAGData.M5) AS M6,
SUM(GLAGData.M7 - GLAGData.M6) AS M7,
SUM(GLAGData.M8 - GLAGData.M7) AS M8,
SUM(GLAGData.M9 - GLAGData.M8) AS M9,
SUM(GLAGData.M10 - GLAGData.M9) AS M10,
SUM(GLAGData.M11 - GLAGData.M10) AS M11,
SUM(GLAGData.M12 - GLAGData.M11) AS M12
FROM
dbo.Export_AccountGroup AS AG
INNER JOIN dbo.Export_GL_AccountGroup_Data AS GLAGData
ON (AG.AccountGroup_ID = GLAGData.AccountGroup_ID)
INNER JOIN dbo.Export_Versions AS EV
ON (GLAGData.Version_ID = EV.Version_ID)
WHERE
(AG.GroupMeat_Name = N'COGS')
GROUP BY
EV.Version_Name,
GLAGData.FISCALYEAR
ELSE
SELECT
'COGS' AS Type,
'Plan Basis' AS [Version Name],
'2007' as [FiscalYear],
'0' AS M1,
'0' AS M2,
'0' AS M3,
'0' AS M4,
'0' AS M5,
'0' AS M6,
'0' AS M7,
'0' AS M8,
'0' AS M9,
'0' AS M10,
'0' AS M11,
'0' AS M12
) dt1
UNION ALL
select
Type,
Version_Name,
FISCALYEAR,
M1,
M2,
M3,
M4,
M5,
M6,
M7,
M8,
M9,
M10,
M11,
M12
from (
IF EXISTS (SELECT
AG.GroupMeat_Name,
GLAGData.AccountGroup_ID
FROM
Export_AccountGroup AS AG
INNER JOIN Export_GL_AccountGroup_Data AS GLAGData
ON (AG.AccountGroup_ID = GLAGData.AccountGroup_ID)
WHERE
(AG.GroupMeat_Name = N'REVENUE') OR
(AG.GroupMeat_Name = N'Other Income') OR
(AG.GroupMeat_Name = N'Interest Income') OR
(AG.GroupMeat_Name = N'Gain on Disposal of Assets') OR
(AG.GroupMeat_Name = N'Returns & Allowances'))
SELECT
'Revenue' AS Type,
EV_1.Version_Name,
GLAGData_1.FISCALYEAR,
SUM(GLAGData_1.M1) * - 1 AS M1,
SUM(GLAGData_1.M2 - GLAGData_1.M1) * - 1 AS M2,
SUM(GLAGData_1.M3 - GLAGData_1.M2) * - 1 AS M3,
SUM(GLAGData_1.M4 - GLAGData_1.M3) * - 1 AS M4,
SUM(GLAGData_1.M5 - GLAGData_1.M4) * - 1 AS M5,
SUM(GLAGData_1.M6 - GLAGData_1.M5) * - 1 AS M6,
SUM(GLAGData_1.M7 - GLAGData_1.M6) * - 1 AS M7,
SUM(GLAGData_1.M8 - GLAGData_1.M7) * - 1 AS M8,
SUM(GLAGData_1.M9 - GLAGData_1.M8) * - 1 AS M9,
SUM(GLAGData_1.M10 - GLAGData_1.M9) * - 1 AS M10,
SUM(GLAGData_1.M11 - GLAGData_1.M10) * - 1 AS M11,
SUM(GLAGData_1.M12 - GLAGData_1.M11) * - 1 AS M12
FROM
dbo.Export_AccountGroup AS AG_1
INNER JOIN dbo.Export_GL_AccountGroup_Data AS GLAGData_1
ON (AG_1.AccountGroup_ID = GLAGData_1.AccountGroup_ID)
INNER JOIN dbo.Export_Versions AS EV_1
ON (GLAGData_1.Version_ID = EV_1.Version_ID)
WHERE
(AG_1.GroupMeat_Name = N'REVENUE') OR
(AG_1.GroupMeat_Name = N'Other Income') OR
(AG_1.GroupMeat_Name = N'Interest Income') OR
(AG_1.GroupMeat_Name = N'Gain on Disposal of Assets') OR
(AG_1.GroupMeat_Name = N'Returns & Allowances')
GROUP BY
EV_1.Version_Name,
GLAGData_1.FISCALYEAR
ELSE
SELECT
'Revenue' AS Type,
'Plan Basis' AS [Version Name],
'2007' as [FiscalYear],
'0' AS M1,
'0' AS M2,
'0' AS M3,
'0' AS M4,
'0' AS M5,
'0' AS M6,
'0' AS M7,
'0' AS M8,
'0' AS M9,
'0' AS M10,
'0' AS M11,
'0' AS M12
) dt2
-
I appreciate everyone's answers and I am going to play with them to see if I can get it to work. In the meantime, the requirements for what I am doing changed somewhat so this isn't mission critical anymore. But it'll be something I want to use in the future.
Thanks!
Paul
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply