Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
ptah
Starting Member
3 Posts |
Posted - 2007-01-18 : 11:29:29
|
hello all,i am trying to create a view but i keep getting the error 'View definition includes no output columns or no items in the FROM clause.'below is the select statement that's the basis of my view. the explanation i got from the F1 help of enterprise manager was ...View definition includes no output columns or no items in the FROM clause.A view definition must have at least one table or table-structured object in the FROM clause, and must have at least one column in the select list. The view definition is missing one or both. Modify the view definition accordingly.query:select Case_CaseId, Logged, CAST(DATEDIFF(minute, Logged, Waiting)/60.0 AS NUMERIC(9, 2)) AS Waiting, CAST(DATEDIFF(minute, Logged, Investigating) /60.0 AS NUMERIC(9, 2)) AS Investigating, CAST(DATEDIFF(minute, Logged, Rejected) /60.0 AS NUMERIC(9, 2)) AS Rejected, CAST(DATEDIFF(minute, Logged, Resolved) /60.0 AS NUMERIC(9, 2)) AS Resolved, CAST(DATEDIFF(minute, Logged, Solved) /60.0 AS NUMERIC(9, 2)) AS Solved, CAST(DATEDIFF(minute, Logged, Closed) /60.0 AS NUMERIC(9, 2)) AS Closedfrom( SELECT Case_CaseId, MIN(CASE WHEN case_stage = 'Logged' THEN Case_CreatedDate END) AS Logged, MIN(CASE WHEN case_stage = 'Waiting' THEN Case_CreatedDate END) AS Waiting, MIN(CASE WHEN case_stage = 'Investigating' THEN Case_CreatedDate END) AS Investigating, AS Rejected, MIN(CASE WHEN case_stage = 'Resolved' THEN Case_CreatedDate END) AS Resolved, MIN(CASE WHEN case_stage = 'Solved' THEN Case_CreatedDate END) AS Solved, MIN(CASE WHEN case_stage = 'Closed' THEN Case_CreatedDate END) AS Closed FROM CaseProgress GROUP BY Case_CaseId) as temporder by Case_CaseId |
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-18 : 13:20:15
|
You're not going to be able to edit that view in Enterprise Manager (the query editor in EM doesn't work with derived tables). You can create it in EM but you'll get errors that you'll have to ignore, so you'd be better off just editing it as a script in Query Analyzer. Also note that you cannot have an ORDER BY in the query in a view. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
|