IF ELSE Error when using with "with tmp as select"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

IF ELSE Error when using with "with tmp as select"

Author

Topic

satisfire
Starting Member

4 Posts

Posted - 2011-05-13 : 04:06:11

Hi
i'm stuck with error "Syntax error near END "with the "END" of below if statement

Here is my code
IF @moreRow = 1
BEGIN

WITH tmpData AS
(
SELECT
[ReportDate]
, [Meter]
, [CMeter]
, [Diff]
, [Billing]
, [Remark]
, dbo.GetReportStatusText([Status]) as [Status]
FROM Ethylene
WHERE MeterID = dbo.GetMeterIDByReportID(@reportID)
--and Year(ReportDate) = @year and Month(ReportDate) = @month
and ReportDate between @periodFirstDate and @periodLastDate
and ((@reportType = 2 and Status = 2) or (@reportType = 1))
UNION
SELECT
[Date_Time] as ReportDate
, 0 as Meter
, [RawData] as CMeter
, 0 as Diff
, NULL as Billing
, [Remark]
, dbo.GetReportStatusText([Status]) as [Status]
FROM Day1ForReport
WHERE MeterID = dbo.GetMeterIDByReportID(@reportID)
and Date_time = @dataNextMonth
and ((@reportType = 2 and Status = 2) or (@reportType = 1))
)
END
ELSE
BEGIN
WITH tmpData AS
(
SELECT
[ReportDate]
, [Meter]
, [CMeter]
, [Diff]
, [Billing]
, [Remark]
, dbo.GetReportStatusText([Status]) as [Status]
FROM Ethylene
WHERE MeterID = dbo.GetMeterIDByReportID(@reportID)
--and Year(ReportDate) = @year and Month(ReportDate) = @month
and ReportDate between @periodFirstDate and @periodLastDate
and ((@reportType = 2 and Status = 2) or (@reportType = 1))
)
END <-- Error here

Anyone know how to correct the syntax ??? Please advise

Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-13 : 04:12:58

You are using CTE and CTE is not supported in SQL Server 2000 only for SQL 2005 onwards. But even then your query is not complete and will not be able to run on SQL 2005

Not sure what you wanted, try removing the "With tmpdata as (" and the ending ")" and see

KH
[spoiler]Time is always against us[/spoiler]

satisfire
Starting Member

4 Posts

Posted - 2011-05-13 : 04:21:57

the thins i want to do is.
Select something and keep them into tmpData by using "With tmpData as ( select ...)"

but the tmpData should be different if @moreRow is 0 or 1

So, is there another way to do as i said?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-13 : 04:22:33

use table variable or temp table

KH
[spoiler]Time is always against us[/spoiler]

satisfire
Starting Member

4 Posts

Posted - 2011-05-13 : 04:41:31

Yeah!! Thank a lot for your advise

it work when i change "WITH tmpdata as" to "select * into tmpdata"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-13 : 04:47:15