Not sure why this doesn't work?

Hello,

I'm using MSSQL 2008 with the following query. The following query runs fine, but when I save this to a view in Microsoft Management Studio, the query changes and doesn't work - with "Error converting varchar to bigint". :confused:

Any ideas out there?

Works:

Code:

---

SELECT HospitalID, Hospital, ROUND((Points+Bonus)/CAST(Number AS BIGINT),0) AS 'Total'
FROM
        (SELECT  A.HospitalID,
                        B.Hospital,
                        (SELECT COUNT(*) FROM users C WHERE C.HospitalID = A.HospitalID GROUP BY C.HospitalID) AS 'Number',
                        ISNULL(SUM(Points),0) AS 'Points',
                        ISNULL((SELECT SUM(Points) FROM teambonus D WHERE D.HospitalID = A.HospitalID GROUP BY D.HospitalID),0) AS 'Bonus'
        FROM activity A JOIN hospitals B ON A.HospitalID = B.ID
        GROUP BY A.HospitalID, B.Hospital) E

---

Doesn't work:

Code:

---

SELECT    HospitalID, Hospital, ROUND(('Points' + 'Bonus') / CAST('Number' AS BIGINT), 0) AS Total
FROM        (SELECT    A.HospitalID, B.Hospital,
                                                  (SELECT    COUNT(*) AS Expr1
                                                    FROM          dbo.users AS C
                                                    WHERE      (HospitalID = A.HospitalID)
                                                    GROUP BY HospitalID) AS 'Number', ISNULL(SUM(A.Points), 0) AS 'Points', ISNULL
                                                  ((SELECT    SUM(Points) AS Expr1
                                                      FROM        dbo.teambonus AS D
                                                      WHERE    (HospitalID = A.HospitalID)
                                                      GROUP BY HospitalID), 0) AS 'Bonus'
                      FROM          dbo.activity AS A INNER JOIN
                                              dbo.hospitals AS B ON A.HospitalID = B.ID
                      GROUP BY A.HospitalID, B.Hospital) AS E

---

may be beause you are missing ' around Total it should be 'Total'

and have ' round 'Points' + 'Bonus' it should be Points + Bonus

Thanks for this.

I manged to create the VIEW (from the first Query) by using a CREATE VIEW statement rather than using Management Studio.

Not sure why the editor changes it?

Thanks again