Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 894
  • Last Modified:

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

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
Jim Horn
Asked:
Jim Horn
2 Solutions
 
Brian CroweDatabase AdministratorCommented:
(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
 
HilaireCommented:
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
 
ptjcbCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now