Question IF...THEN...ELSE statement within SQL UPDATE SELECT

[Simply Me]

Hi!


I would like to compute for the general average of a student who belong to a certain class section. The flow of query will look like

IF SectionName='Section1' THEN
   update a table field GenAve using a formula
ELSEIF SectionName="Section2" THEN
   update a table field GenAve using another formula
ElSE
   update a table field GenAve using another formula
END IF

Is this possible?

I was trying to do it like this

If SectionName = 'Section1'
	UPDATE tblgradek12 SET GenAve=((Speech * 1.2) + 
									(English * 1.5) + 
									(Math * 1.5) + 
									(Science * 1.8) + 
									(PE * 1.2))/7.2 
	SELECT DISTINCT IDNO,LastName,FirstName,MI,GENDER,
		YEARLEVEL,SECTIONNAME,Adviser,[English],[Math],[Speech], 
		[PE],[Science],[Earth Science]
	FROM tblGradeK12
ELSE
if SectionName = 'Section2'
	UPDATE tblgradek12 SET GenAve=((Speech * 1.2) + 
									(English * 1.5) + 
									(Math * 1.5) + 
									(Science * 1.8) + 
									(PE * 1.2) +
									([Earth Science] * 1))/8.2 
	SELECT DISTINCT IDNO,LastName,FirstName,MI,GENDER,
		YEARLEVEL,SECTIONNAME,Adviser,[English],[Math],[Speech], 
		[PE],[Science],[Earth Science]
	FROM tblGradeK12

 

[IanRyder]

Hi,

Sorry but I do not know what you mean by "I just need to be clarified with this against what I did...I just put [GenAve] [numeric](5, 3) NULL, in my SPROC"

If you still have an issue then please explain what you mean by this is detail otherwise just let us know that all is OK.

Cheers,

Ian

 

[Simply Me]

Actually I'm all sorted out. What I meant was I declared [GenAve] [numeric](5, 3) NULL, instead of using the CAST(ROUND(123.4567891,3) as Decimal(18,3)). Is the latter better than the other?

 

[IanRyder]

Hi,

No, that seems fine and you know since it works.

Anyway, thanks for letting us know.

Cheers,

Ian

 

[Simply Me]

Many thanks for your patience and help too Ian...