Spot the syntax error - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Spot the syntax error

Author

Topic

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-16 : 09:01:41

Lets play spot the error! Can anyone see a syntax error in this? (FromDates and StartDates are for your benefits before any smart so and so comments on them)

Set Transaction Isolation Level Read Uncommitted

Declare @StartDate as DateTime
Declare @EndDate as DateTime
Declare @FromDate as DateTime
Declare @ToDate as DateTime

Set @StartDate = <%FromDate|StartDate%>
Set @EndDate = <%ToDate|EndDate%>
Set @FromDate = dbo.fUniversalTime(@StartDate)
Set @ToDate = dbo.fUniversalTime(@EndDate)

Declare @ChilternVale as VarChar

Select Count(*),
Case When (
pg.Name like '%(040079)' or
pg.Name like '%(040072)' or
pg.Name like 'Intrahealth%' or
pg.Name like '%(040066)' or
pg.Name like '%(040030)' or
pg.Name like '%(040047)' or
pg.Name like '%(040009)' or
pg.Name like '%(040003)' or
pg.Name like '%(040063)' or
pg.Name like '%(040077)' )

End as @ChilternVale

From [case] c

Join ProviderGroup pg on pg.ProviderGroupRef = c.ProviderGroupRef

Where c.ActiveDate between @FromDate and @ToDate and c.Cancelled = 0 and c.TestCall = 0

Group By @ChilternVale

I would use a temporary table for everything ever

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-16 : 09:08:07

Set @StartDate = <%FromDate|StartDate%>
Set @EndDate = <%ToDate|EndDate%>

What is that? I've never seen that syntax before.

Jim

Everyday I learn something that somebody else already knew

Sachin.Nand

2937 Posts

Posted - 2009-09-16 : 09:40:53

Is that SQL???
Well if it is then I can spot that all your case statement is messed up & also the "then" keyword missing in the case statement.

PBUH

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-16 : 09:53:51

Set @StartDate = <%FromDate|StartDate%>
Set @EndDate = <%ToDate|EndDate%>

That sets date filters in the 3rd party software I am using for when users run the report so they can easily select a date range.

Cheers Idera, I knew it would be a stupid mistake. xD

What is wrong with my case statement?

case when ... then ... else ... end as ...

?? I newbed it up on here

I would use a temporary table for everything ever

Sachin.Nand

2937 Posts

Posted - 2009-09-16 : 10:01:16

First of all u can cannot use a variable in the group by clause the way u did it here

Group By @ChilternVale

Try this one

Select Count(*),
@ChilternVale=Case When
pg.Name like '%(040079)' or
pg.Name like '%(040072)' or
pg.Name like 'Intrahealth%' or
pg.Name like '%(040066)' or
pg.Name like '%(040030)' or
pg.Name like '%(040047)' or
pg.Name like '%(040009)' or
pg.Name like '%(040003)' or
pg.Name like '%(040063)' or
pg.Name like '%(040077)' then 'SOMETHING' ELSE 'NOTHING'

End

From [case] c

Join ProviderGroup pg on pg.ProviderGroupRef = c.ProviderGroupRef

Where c.ActiveDate between @FromDate and @ToDate and c.Cancelled = 0 and c.TestCall = 0

Group By pg.Name

PBUH

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-16 : 12:13:22

cheers boss

I would use a temporary table for everything ever

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources