?
Solved

View errmsg 'View definition includes no output columns or includes no items in the FROM clause'

Posted on 2005-02-27
4
Medium Priority
?
892 Views
Last Modified: 2013-11-18
The below SQL works in Query Designer, works when I hit 'Run' in the View Designer, and all calculated fields are aliased, but for some reason I still get the errmsg in the title, and it will not allow me to save the view.  
TIA -Jim
---------------------
SELECT     tdds.month_id, tdds.chmd_disease_state_cd, ldept.source_cd, Round(SUM(tdds.month_0_margin), 0) AS month_0_margin,
                      Round(SUM(tdds.month_1_margin), 0) AS month_1_margin, Round(SUM(tdds.month_2_margin), 0) AS month_2_margin,
                      Round(SUM(tdds.month_3_margin), 0) AS month_3_margin, Round(SUM(tdds.month_4_margin), 0) AS month_4_margin,
                      Round(SUM(tdds.month_5_margin), 0) AS month_5_margin, Round(SUM(tdds.month_6_margin), 0) AS month_6_margin,
                      Round(SUM(tdds.month_7_margin), 0) AS month_7_margin, Round(SUM(tdds.month_8_margin), 0) AS month_8_margin,
                      Round(SUM(tdds.month_9_margin), 0) AS month_9_margin, Round(SUM(tdds.month_10_margin), 0) AS month_10_margin,
                      Round(SUM(tdds.month_11_margin), 0) AS month_11_margin, Round(SUM(tdds.month_12_margin), 0) AS month_12_margin
FROM         dbo.er_trpt_department_by_disease_state tdds
INNER JOIN  dbo.er_lkp_departments ldept ON tdds.department_id = ldept.department_id
GROUP BY tdds.month_id, tdds.chmd_disease_state_cd, ldept.source_cd

UNION
SELECT     tdds.month_id, tdds.chmd_disease_state_cd, 0 AS source_cd, Round(SUM(tdds.month_0_margin), 0) AS month_0_margin,
                      Round(SUM(tdds.month_1_margin), 0) AS month_1_margin, Round(SUM(tdds.month_2_margin), 0) AS month_2_margin,
                      Round(SUM(tdds.month_3_margin), 0) AS month_3_margin, Round(SUM(tdds.month_4_margin), 0) AS month_4_margin,
                      Round(SUM(tdds.month_5_margin), 0) AS month_5_margin, Round(SUM(tdds.month_6_margin), 0) AS month_6_margin,
                      Round(SUM(tdds.month_7_margin), 0) AS month_7_margin, Round(SUM(tdds.month_8_margin), 0) AS month_8_margin,
                      Round(SUM(tdds.month_9_margin), 0) AS month_9_margin, Round(SUM(tdds.month_10_margin), 0) AS month_10_margin,
                      Round(SUM(tdds.month_11_margin), 0) AS month_11_margin, Round(SUM(tdds.month_12_margin), 0) AS month_12_margin
FROM dbo.er_trpt_department_by_disease_state tdds
INNER JOIN dbo.er_lkp_departments ldept ON tdds.department_id = ldept.department_id
GROUP BY tdds.month_id, tdds.chmd_disease_state_cd
0
Comment
Question by:Jim Horn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13415620
(SELECT     tdds.month_id, tdds.chmd_disease_state_cd, ldept.source_cd, Round(SUM(tdds.month_0_margin), 0) AS month_0_margin,
                      Round(SUM(tdds.month_1_margin), 0) AS month_1_margin, Round(SUM(tdds.month_2_margin), 0) AS month_2_margin,
                      Round(SUM(tdds.month_3_margin), 0) AS month_3_margin, Round(SUM(tdds.month_4_margin), 0) AS month_4_margin,
                      Round(SUM(tdds.month_5_margin), 0) AS month_5_margin, Round(SUM(tdds.month_6_margin), 0) AS month_6_margin,
                      Round(SUM(tdds.month_7_margin), 0) AS month_7_margin, Round(SUM(tdds.month_8_margin), 0) AS month_8_margin,
                      Round(SUM(tdds.month_9_margin), 0) AS month_9_margin, Round(SUM(tdds.month_10_margin), 0) AS month_10_margin,
                      Round(SUM(tdds.month_11_margin), 0) AS month_11_margin, Round(SUM(tdds.month_12_margin), 0) AS month_12_margin
FROM         dbo.er_trpt_department_by_disease_state tdds
INNER JOIN  dbo.er_lkp_departments ldept ON tdds.department_id = ldept.department_id
GROUP BY tdds.month_id, tdds.chmd_disease_state_cd, ldept.source_cd) A
UNION
(SELECT     tdds.month_id, tdds.chmd_disease_state_cd, 0 AS source_cd, Round(SUM(tdds.month_0_margin), 0) AS month_0_margin,
                      Round(SUM(tdds.month_1_margin), 0) AS month_1_margin, Round(SUM(tdds.month_2_margin), 0) AS month_2_margin,
                      Round(SUM(tdds.month_3_margin), 0) AS month_3_margin, Round(SUM(tdds.month_4_margin), 0) AS month_4_margin,
                      Round(SUM(tdds.month_5_margin), 0) AS month_5_margin, Round(SUM(tdds.month_6_margin), 0) AS month_6_margin,
                      Round(SUM(tdds.month_7_margin), 0) AS month_7_margin, Round(SUM(tdds.month_8_margin), 0) AS month_8_margin,
                      Round(SUM(tdds.month_9_margin), 0) AS month_9_margin, Round(SUM(tdds.month_10_margin), 0) AS month_10_margin,
                      Round(SUM(tdds.month_11_margin), 0) AS month_11_margin, Round(SUM(tdds.month_12_margin), 0) AS month_12_margin
FROM dbo.er_trpt_department_by_disease_state tdds
INNER JOIN dbo.er_lkp_departments ldept ON tdds.department_id = ldept.department_id
GROUP BY tdds.month_id, tdds.chmd_disease_state_cd) B
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 1920 total points
ID: 13418086
Hi jimhorn,
first of all I think your query could be simplified as follows using GROUP BY WITH ROLLUP
to avoid the overhead of a UNION

Please try

SELECT tdds.month_id, tdds.chmd_disease_state_cd,
case when grouping(ldept.source_cd)=1 then 0 else ldept.source_cd end as source_cd,
      Round(SUM(tdds.month_0_margin), 0) AS month_0_margin,
      Round(SUM(tdds.month_1_margin), 0) AS month_1_margin, Round(SUM(tdds.month_2_margin), 0) AS month_2_margin,
      Round(SUM(tdds.month_3_margin), 0) AS month_3_margin, Round(SUM(tdds.month_4_margin), 0) AS month_4_margin,
      Round(SUM(tdds.month_5_margin), 0) AS month_5_margin, Round(SUM(tdds.month_6_margin), 0) AS month_6_margin,
      Round(SUM(tdds.month_7_margin), 0) AS month_7_margin, Round(SUM(tdds.month_8_margin), 0) AS month_8_margin,
      Round(SUM(tdds.month_9_margin), 0) AS month_9_margin, Round(SUM(tdds.month_10_margin), 0) AS month_10_margin,
      Round(SUM(tdds.month_11_margin), 0) AS month_11_margin, Round(SUM(tdds.month_12_margin), 0) AS month_12_margin
FROM dbo.er_trpt_department_by_disease_state tdds
INNER JOIN  dbo.er_lkp_departments ldept ON tdds.department_id = ldept.department_id
GROUP BY tdds.month_id, tdds.chmd_disease_state_cd, ldept.source_cd
WITH ROLLUP
HAVING grouping(tdds.month_id) = 0 AND grouping(tdds.chmd_disease_state_cd) = 0

If you still prefer your version, I'd suggest using UNION ALL rather than UNION

As a side note, you should create your views in Query Analyser rather than Enterprise Manager.
The query designer in EM really sucks ... it won't let you create a view using case ...when or using a syntax more complicated than a very basic select.

HTH
Hilaire

0
 
LVL 27

Assisted Solution

by:ptjcb
ptjcb earned 80 total points
ID: 13421081
I agree with Hilaire - Avoid using the tools in EM when you are designing queries. You will find all sorts of odd errors that will make your hair gray. Use Query Analyzer for query design. I know if does not have the same tools, but learning t-sql will be a good thing for your sanity if you intend to work with SQL Server.
0
 
LVL 66

Author Comment

by:Jim Horn
ID: 13422057
Hilaire - Thank you for your SQL, it worked perfectly.  Coming into T-SQL from Access VBA, CUBE and ROLLUP are new to me, but I'm starting to like it awfully quick.

BriCrowe - I tried your SQL and received the following result
Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'A'.
Server: Msg 170, Level 15, State 1, Line 21
Line 21: Incorrect syntax near 'B'.
0

Featured Post

7 Extremely Useful Linux Commands for Beginners

Just getting started with Linux? Here's a quick start guide that has 7 commands that we believe will come in handy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question