SQL CTE Question
April 18, 2008 12:22 PM Subscribe
I need help with a SQL CTE which works in SQL 2005 but doesn't in Business Objects.
So I have this lovely little CTE which I need to use in, ultimately, creating a derived table on which I'll base a Business Objects Performance Manager analytic. The code works when I test in other SQL applications. It doesn't work in the Business Objects universe. Is there another way, without using aliases, to get Business Objects to do what I have this bit of code doing?
Here's the code in question:
WITH Campaigns(Id)
AS
(
SELECT CAMPAIGN_HIERARCHY_ID FROM CAMPAIGN_HIERARCHY WHERE CH_NEXT_LEVEL_CAMPAIGN = 'A'
UNION ALL
SELECT CH.CAMPAIGN_HIERARCHY_ID FROM CAMPAIGN_HIERARCHY CH JOIN Campaigns C
ON C.Id = CH.CH_NEXT_LEVEL_CAMPAIGN
)
SELECT Id INTO dbo.A_CAMPAIGN_STRUCTURE FROM Campaigns
INSERT INTO dbo.A_CAMPAIGN_STRUCTURE (Id) VALUES ('A')
So I have this lovely little CTE which I need to use in, ultimately, creating a derived table on which I'll base a Business Objects Performance Manager analytic. The code works when I test in other SQL applications. It doesn't work in the Business Objects universe. Is there another way, without using aliases, to get Business Objects to do what I have this bit of code doing?
Here's the code in question:
WITH Campaigns(Id)
AS
(
SELECT CAMPAIGN_HIERARCHY_ID FROM CAMPAIGN_HIERARCHY WHERE CH_NEXT_LEVEL_CAMPAIGN = 'A'
UNION ALL
SELECT CH.CAMPAIGN_HIERARCHY_ID FROM CAMPAIGN_HIERARCHY CH JOIN Campaigns C
ON C.Id = CH.CH_NEXT_LEVEL_CAMPAIGN
)
SELECT Id INTO dbo.A_CAMPAIGN_STRUCTURE FROM Campaigns
INSERT INTO dbo.A_CAMPAIGN_STRUCTURE (Id) VALUES ('A')
This thread is closed to new comments.
posted by orthogonality at 1:47 PM on April 18, 2008