Systax Error

Question

Post reply

Systax Error

Trybbe

Hall of Fame

Points: 3967
More actions
March 24, 2010 at 5:16 am

#156817

hi may you please tell me what is wrong with this code
Declare
@Cluster Varchar(max),
@Consolidate Varchar(max),
@Level3 Varchar(max),
@transferType Varchar(15)
Set @Cluster = 'Nedbank Retail'
Set @Consolidate = 'NRD Bank Services'
Set @transferType = 'TransferIn'
--Print @TransferType
Begin
IF @Consolidate = '(CONSOLIDATE)'
selectre.Cluster, re.Consolidate, re.Level3, re.Division, re.Region, re.Area, re.Branch, re.CostCenter,
dt.Staffno, dt.CallName, dt.Surname, dt.Race, dt.Gender, gg.[HC CAT1], dt.Headcount, dt.TransferType,dt.Period
FROMDTIHeadcount dt inner join ReportingStructure re
ON dt.CostCenter = re.CostCenter
inner join [Grade Grouping] gg ON dt.GradeCode = gg.GradeID
Where(dt.Period between 201001 and 201002)
and(re.Cluster = @Cluster)
and ((re.Consolidate In (@Consolidate)) OR (@Consolidate = '(Consolidate)'))
--and((case when gg.[HC CAT1] ='Permanent' then 'Permanent' else 'Non Permanent' end IN (@HCCAT1)) OR (@HCCAT1 = 'Staff'))
and((dt.Headcount = Case when @TransferType = 'Termination' then 'Termination'
when @TransferType = 'NewHire' then 'NewHire'
when @TransferType = 'NoMove' then 'Headcount' End)
OR (dt.Headcount in ('TransferCluster')))
and(dt.TransferType = @TransferType)
ORDER BYgg.[HC CAT1], re.Consolidate
if @Consolidate <> '(CONSOLIDATE)' and @Level3 = '(LEVEL3)'
selectre.Cluster, re.Consolidate, re.Level3, re.Division, re.Region, re.Area, re.Branch, re.CostCenter,
dt.Staffno, dt.CallName, dt.Surname, dt.Race, dt.Gender, gg.[HC CAT1], dt.Headcount, dt.TransferType,dt.Period
FROMDTIHeadcount dt inner join ReportingStructure re
ON dt.CostCenter = re.CostCenter
inner join [Grade Grouping] gg ON dt.GradeCode = gg.GradeID
Where(dt.Period between 201001 and 201002)
and(re.Cluster = @Cluster)
and ((re.Consolidate In (@Consolidate)) OR (@Consolidate = '(Consolidate)'))
and (@Level3 = '(Level3)')
--and((case when gg.[HC CAT1] ='Permanent' then 'Permanent' else 'Non Permanent' end IN (@HCCAT1)) OR (@HCCAT1 = 'Staff'))
and((dt.Headcount = Case when @TransferType = 'Termination' then 'Termination'
when @TransferType = 'NewHire' then 'NewHire'
when @TransferType = 'NoMove' then 'Headcount' End)
OR (dt.Headcount in ('TransferCluster', 'TransferConsolidate')))
and(dt.TransferType = @TransferType)
ORDER BY gg.[HC CAT1], re.Level3
End

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

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

Paul White SSC Guru Points: 150457 More actions · Answer 1

Did you receive an error message when trying to run it?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

doesn't give an error and doesnt' give results.

this is all I get "Command(s) completed successfully."

LythamsFinest SSCommitted Points: 1786 More actions · Answer 3

You have not supplied a value for the @Level3 variable so the 2nd IF statement will be false just like the 1st one.

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

Trybbe

Hall of Fame

Points: 3967

March 24, 2010 at 7:18 am

#1138897

thank you member that solves it.

kind regards