how to use (with) statment

Question

how to use (with)  statment  in sql 2000 ??

i have this code in sql 2008 but it dos't work in sql 2000

how to make it work without i losse my time to make new view

code in 2008

{

CREATE VIEW [dbo].[QRateDefMonumPrice]
AS
WITH ETC AS (SELECT     RateDefID, SUM(Admission) AS SumAdmission
                                FROM         dbo.QRateDefMonumDaysPrice
                                GROUP BY RateDefID)
    SELECT     RateDefID, SumAdmission, CASE WHEN [SumAdmission] != (FLOOR([SumAdmission])) THEN (FLOOR([SumAdmission]) + 1) ELSE (FLOOR([SumAdmission]))
                            END AS FixSumAdmission
     FROM         ETC AS ETC_1

GO

}

 with dos't work in sql 2000

Answer 1

WITH statement is added from SQL 2005 and so it won't work in SQL 2000.

This is used to create Common Table Expressions (derived tables) that can be used directly within its adjoining SELECT statement.

Try the below query modified to work with SQL 2000 (CTE replaced as derived table).

CREATE VIEW [dbo].[QRateDefMonumPrice]
 AS

SELECT  RateDefID, SumAdmission, 
		CASE 
			WHEN [SumAdmission] != (FLOOR([SumAdmission])) 
				THEN (FLOOR([SumAdmission]) + 1) 
			ELSE (FLOOR([SumAdmission]))
        END AS FixSumAdmission
FROM    (
			SELECT  RateDefID, 
					SUM(Admission) AS SumAdmission
			FROM    dbo.QRateDefMonumDaysPrice
			GROUP BY RateDefID	
		) AS ETC_1
 
GO

Thanks!

Answer 2

The With is a common table Expression and doesn't exist in older version like SQL Server 2000, for SQL Server 2000 use the below code:

CREATE VIEW [dbo].[QRateDefMonumPrice]
AS
  SELECT     RateDefID, SUM(Admission) AS SumAdmission into #Temp1
                                FROM         dbo.QRateDefMonumDaysPrice
                                GROUP BY RateDefID)
    SELECT     RateDefID, SumAdmission, CASE WHEN [SumAdmission] != (FLOOR([SumAdmission])) THEN (FLOOR([SumAdmission]) + 1) ELSE (FLOOR([SumAdmission]))
                            END AS #Temp1
     FROM         ETC AS ETC_1

GO

}

Answer 3

CREATE VIEW [dbo].[QRateDefMonumPrice]
AS
  SELECT     RateDefID, SUM(Admission) AS SumAdmission into #Temp1
                                FROM         dbo.QRateDefMonumDaysPrice
                                GROUP BY RateDefID)
    SELECT     RateDefID, SumAdmission, CASE WHEN [SumAdmission] != (FLOOR([SumAdmission])) THEN (FLOOR([SumAdmission]) + 1) ELSE (FLOOR([SumAdmission]))
                            END AS #Temp1
     FROM         ETC AS ETC_1

GO

}

SELECT...INTO is not valid in a view so this proposed solution will not work.  Deepak's suggestion to use a derrived table should do the job.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/