Translating Access SQL Statement to T-SQL
-
I have the following code, which i generated using Access DB, i need to translate it to T-SQL, am mainly getting error on the IIF statement SELECT dbo.casyAgeGroups.Sequence, dbo.casyAgeGroups.Description, Sum(IIf([gender]='Male' And [age]>=[startage] And [age]<=[endage],1,0)) AS Male, Sum(IIf([gender]='Female' And [age]>=[startage] And [age]<=[endage],1,0)) AS Female FROM dbo.casyAgeGroups, [qryStats-AgeGenderProfileDetails] GROUP BY dbo.casyAgeGroups.Sequence, dbo.casyAgeGroups.Description, [qryStats-AgeGenderProfileDetails].EntityType HAVING ((([qryStats-AgeGenderProfileDetails].EntityType)=0));
-
I have the following code, which i generated using Access DB, i need to translate it to T-SQL, am mainly getting error on the IIF statement SELECT dbo.casyAgeGroups.Sequence, dbo.casyAgeGroups.Description, Sum(IIf([gender]='Male' And [age]>=[startage] And [age]<=[endage],1,0)) AS Male, Sum(IIf([gender]='Female' And [age]>=[startage] And [age]<=[endage],1,0)) AS Female FROM dbo.casyAgeGroups, [qryStats-AgeGenderProfileDetails] GROUP BY dbo.casyAgeGroups.Sequence, dbo.casyAgeGroups.Description, [qryStats-AgeGenderProfileDetails].EntityType HAVING ((([qryStats-AgeGenderProfileDetails].EntityType)=0));
The
IIFfunction was added in SQL 2012[^]. If you're using an earlier version, you'll need to replace theIIFfunction with a CASE block[^]:Sum(CASE
WHEN gender = 'Male' And age >= startage And age <= endage THEN 1
ELSE 0
END)You could also simplify the
agecondition by using the BETWEEN operator[^]:Sum(CASE
WHEN gender = 'Male' And age Between startage And endage THEN 1
ELSE 0
END)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
