Problem trying to create a view with WITH

advertisements

I'm trying to create the following view, the query runs find until I try to create a View and then I get an error which states: Create View or Function failed because no column name was specified for column 1, research I've found hasn't really helped as it's usually something simple wrong with the query but mine runs fine on its own currently. Any insight?

CREATE VIEW dbo.prov_prod_rv AS

WITH rep_prods AS (SELECT t.amount, t.provider_id, t.clinic, p.period_start, t.impacts,
p.period_end, p.period_id, p.fiscal_year, p.date_man
FROM dbo.transactions AS t
CROSS JOIN dbo.period AS p
WHERE (t.date_entered BETWEEN p.period_start AND p.period_end))

SELECT SUM(r.amount), r.provider_id, d.first_name, d.last_name, d.position_id, d.clinic, r.period_id, r.fiscal_year, r.date_man
FROM rep_prods AS r INNER JOIN
dbo.provider AS d ON r.provider_id = d.provider_id AND r.clinic = d.clinic
WHERE (r.impacts = 'P') AND (r.amount <> 0)
GROUP BY r.provider_id, d.first_name, d.last_name, d.position_id, d.clinic, r.period_id, r.fiscal_year, r.date_man


The problem is in your final SELECT:

SELECT   SUM(r.amount),
         r.provider_id,
         d.first_name,
         d.last_name,
         d.position_id,
         d.clinic,
         r.period_id,
         r.fiscal_year,
         r.date_man

You need to give a column name to the SUM(r.amount) column:

SELECT   SUM(r.amount) As TotalAmount,
         r.provider_id,
         d.first_name,
         d.last_name,
         d.position_id,
         d.clinic,
         r.period_id,
         r.fiscal_year,
         r.date_man