Syntax Error

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Trybbe Hall of Fame Points: 3967 More actions · Answer 1

It's Ok I fixed it

Declare

@EndPeriod Numeric,

@Consolidate Varchar(200),

@Level3 Varchar(200),

@Division Varchar(200)

Set @EndPeriod = 200912

Set @Consolidate = '(Retail Consolidate)'

Set @Level3 = '(LEVEL3)'

--Set @Division is null

IF @Consolidate = '(Retail Consolidate)'

Begin

SELECT Consolidate as DisplayLevel , COUNT([HC CAT1]) AS cntStaffNO, [HC CAT1], [HC Cat5], [HC CAT6]

FROM HRC_MIS

WHERE (Period = @EndPeriod)

AND (Consolidate IN(Select * From dbo.Split(@Consolidate, ',')) OR (@Consolidate = '(Retail Consolidate)'))

GROUP BY Consolidate, Level3, [HC CAT1], [HC Cat5], [HC CAT6]

ORDER BY [HC CAT1]

End

IF @Consolidate != '(Retail Consolidate)' and @Level3 = '(LEVEL3)'

Begin

SELECT Level3 as DisplayLevel, COUNT([HC CAT1]) AS cntStaffNO, [HC CAT1], [HC Cat5], [HC CAT6]

FROM HRC_MIS

WHERE (Period = @EndPeriod)

AND Consolidate = @Consolidate

AND (Level3 IN (Select * From dbo.Split(@Level3, ',')))

GROUP BY Level3, [HC CAT1], [HC Cat5], [HC CAT6]

ORDER BY Level3

End

ELSE --IF @Level3 <> '(LEVEL3)'

Begin

SELECT Division as DisplayLevel, COUNT([HC CAT1]) AS cntStaffNO, [HC CAT1], [HC Cat5], [HC CAT6]

FROM HRC_MIS

WHERE (Period = @EndPeriod)

AND Level3 IN (Select * From dbo.Split(@Level3, ','))

GROUP BY Division, [HC CAT1], [HC Cat5], [HC CAT6]

ORDER BY Division

End

steveb. SSC-Forever Points: 46733 More actions · Answer 2

you are trying to assign a default value (null) to a local varable

Trybbe Hall of Fame Points: 3967 More actions · Answer 3

Yes that was the problem, you'll notice on my second post that I have removed it.

Thanks for feedback

steveb. SSC-Forever Points: 46733 More actions · Answer 4

sorry I was typing the repsonse when you posted your answer so didn't see it until it was too late.

Glad it is sorted out .