Avatar of Travidia
Travidia asked on

Query won't work as a View

I have this query and I want to set it as a view.  

WITH CMS(Username, Workflow_action, type1, type2, ROPID, AdID, SubRegionID, date_created, ShiftDate) AS (SELECT DISTINCT
username, workflow_action, type1, type2, ROPID, AdID, SubRegionID, date_created, ShiftDate
FROM     dbo.view_FD_QCers
WHERE  (SubRegionID = 0) AND (ShiftDate BETWEEN GETDATE() - 3 AND GETDATE())),

FD(username, AdID, SubRegionID, action_time, ShiftDate) AS
(SELECT DISTINCT username, AdID, SubRegionID, action_time, ShiftDate
FROM     dbo.view_BPOFDSub_QC AS view_FD_QCers_1
WHERE  (SubRegionID <> 0) AND (ShiftDate BETWEEN GETDATE() - 3 AND GETDATE())
Union
SELECT DISTINCT username, AdID, SubRegionID, action_time, ShiftDate
FROM     dbo.view_Chico_FDSub_QC AS view_FD_QCers_1
WHERE  (SubRegionID <> 0) AND ShiftDate BETWEEN GETDATE() - 3 AND GETDATE()
),

FDFinal(UserName, AdID, SubRegionID, ShiftDate) AS
(SELECT DISTINCT Username, AdID, SubRegionID, ShiftDate
FROM     FD AS FD_1
WHERE  (ShiftDate = CAST(CONVERT(varchar(12), GETDATE() - 1, 101) AS datetime))),

CMSFinal(UserName, AdID, SubRegionID, ShiftDate) AS
(SELECT DISTINCT Username, AdID, SubRegionID, ShiftDate
FROM     CMS AS CMS_1
WHERE  (ShiftDate = CAST(CONVERT(varchar(12), GETDATE() - 1, 101) AS datetime)))

SELECT  ShiftDate, UserName, COUNT(AdID) AS AdCount, 0 AS SubCount
FROM     CMSFinal AS CMSFinal_1
GROUP BY ShiftDate, UserName
UNION
SELECT  ShiftDate, UserName, 0 AS AdCount, COUNT(SubRegionID) AS SubCount
FROM     FDFinal AS FDFinal_1
GROUP BY ShiftDate, UserName

When I put it into the query and save it, I get error 'Invalid Field Name FD'
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
dqmq

8/22/2022 - Mon
dqmq

Wow! Desk-checking, I don't see any errors.  I'd suggest simplifying to a single CTE expression, then adding the others back in one at a time until you can isolate the problem.
ASKER
Travidia

He dgmg...  

>> simplifying to a single CTE expression<<

What does that mean?  What do I need to do?
ASKER CERTIFIED SOLUTION
dqmq

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Travidia

Wow.  That worked.  I added the whole query in and it worked.  I've never created a view using a query - I've always user 'Create New Veiw' and pasted in my query.  Is that better or something?  Why would it not error when doing it that way?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
dqmq

>Is that better or something?
Nah...it's like if you can't swat a fly with your right hand, try with your left.

>Why would it not error when doing it that way?
I don't know.  Multiple-CTE queries are rare--in fact I didn't even know the capability existed until I tried debugging your syntax.  But it's a technique I can see plenty of uses for. It's like nested, in-line view--very cool.  I owe you points.

My guess is that you may have found a bug in the view builder.  It would be interested to try a modify view using the view builder and see if that complains, too.