Question Help on SQL Update

[Simply Me]

HI!


My tblenrolk12 has the following fields. LastName, FirstName, MI, SubjectName, FG1, etc...


In my tblegradek12 I have LastName, FirstName, MI, Music, Arts, PE, Health, etc...


These 4 (Music, Arts, PE, Health) were actually a SubjectName field records in tblenrolk12. I created it like

CREATE TABLE [dbo].[tblGradeK12](
    [IDNo] [nvarchar](10) NOT NULL,
    [LastName] [nvarchar](50) NULL,
    [FirstName] [nvarchar](50) NULL,
    [MI] [nvarchar](3) NULL,
    [Music] [numeric](5, 0) NULL,
    [Arts] [numeric](5, 0) NULL,
    [PE] [numeric](5, 0) NULL,
    [Health] [numeric](5, 0) NULL

Now, I want to compute for the grade of the four subjects by getting 25% of each and have it as MAPEH grade.


I tried this code but its not working at all

UPDATE tblgradeK12
SET [MAPEH] =((MUSIC * .25) + (ARTS * .25) + (PE * .25) + (HEALTH * .25))

 

[IanRyder]

Hi,

You do not give any indication why it is not working but the only thing that seems obvious without further information is that the field MAPEH does not exist in the table that you have created.

Hope that helps.

Cheers,

Ian

 

[Simply Me]

Hi IanRyder,
Here's a more complete code that I did.

USE [SY2012-2013L]
GO
/****** Object:  StoredProcedure [dbo].[CreateInsertUpdateK12]    Script Date: 11/03/2012 15:25:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[CreateInsertUpdateK12]


AS


If exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tblGradeK12]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Drop Table [dbo].[tblGradeK12]


CREATE TABLE [dbo].[tblGradeK12](
    [IDNo] [nvarchar](10) NOT NULL,
    [LastName] [nvarchar](50) NULL,
    [FirstName] [nvarchar](50) NULL,
    [MI] [nvarchar](3) NULL,
    [Music] [numeric](5, 0) NULL,
    [Arts] [numeric](5, 0) NULL,
    [PE] [numeric](5, 0) NULL,
    [Health] [numeric](5, 0) NULL,
    [MAPEH] [numeric](5, 0) NULL,
    etc.....


INSERT INTO tblGradeK12
	(
	IDNo, 
	LastName, 
	FirstName, 
	MI, 
	Gender,
	[Address],	
	YearLevel, 
	SectionName,
	Adviser
	)
SELECT DISTINCT (IDNo), 
LastName, 
FirstName, 
MI, 
Gender, 
[Address],
YearLevel, 
SectionName,
Adviser
FROM	tblenrolK12


--MUSIC
UPDATE tblGradeK12
SET MUSIC = tblenrolK12.FG1
FROM tblenrolK12
WHERE tblGradeK12.IDNO = tblenrolK12.IDNO
AND tblGradeK12.yearlevel = tblenrolK12.yearlevel
AND tblenrolK12.SubjectName like 'MUSIC'




--ARTS
UPDATE tblGradeK12
SET ARTS = tblenrolK12.FG1
FROM tblenrolK12
WHERE tblGradeK12.IDNO = tblenrolK12.IDNO
AND tblGradeK12.yearlevel = tblenrolK12.yearlevel
AND tblenrolK12.SubjectName like 'ARTS'




--PE
UPDATE tblGradeK12
SET PE = tblenrolK12.FG1
FROM tblenrolK12
WHERE tblGradeK12.IDNO = tblenrolK12.IDNO
AND tblGradeK12.yearlevel = tblenrolK12.yearlevel
AND tblenrolK12.SubjectName like 'PE'




--HEALTH
UPDATE tblGradeK12
SET HEALTH = tblenrolK12.FG1
FROM tblenrolK12
WHERE tblGradeK12.IDNO = tblenrolK12.IDNO
AND tblGradeK12.yearlevel = tblenrolK12.yearlevel
AND tblenrolK12.SubjectName like 'HEALTH'

--Compute for MAPEH grade.
UPDATE tblGradeK12
SET [MAPEH] =((MUSIC * .25) + (ARTS * .25) + (PE * .25) + (HEALTH * .25))

when I execute this in SSMSE it says "(543 row(s) affected)" but when I checked it if MAPEH is computed, the column has Null values.

UPDATE tblGradeK12
SET [MAPEH] =((MUSIC * .25) + (ARTS * .25) + (PE * .25) + (HEALTH * .25))

 

[IanRyder]

Hi,

Thanks for the additional SQL code that you posted. The only thing that sticks out as an issue now is the impact that NULL values will have on any form of calculation in SQL.

To explain, you create the table, tblGradeK12, you then update this table to add the MUSIC, ARTS, PE and HEALTH fields from the tblenrolK12 table but what you need to consider is whether any of the values in the tblenrolK12 are NULL. If they are then a NULL value will propagate to the tblGradeK12 table.

When SQL then performs calculations on fields which have not correctly dealt with a NULL value then a NULL value will always be returned as the result. To combat this try using ISNULL to check for null values and deal with them appropriately. ISNULL will check for a null value and return a default specified value if a null value is detected. i.e:-

SET [MAPEH] =((ISNULL(MUSIC,0) * .25) + (ISNULL(ARTS,0) * .25) + (ISNULL(PE,0) * .25) + (ISNULL(HEALTH,0) * .25))

You could also deal with any null issues at the point of doing your update statements to each of the fields. i.e:-

SET HEALTH = ISNULL(tblenrolK12.FGI,0)

Hope that helps.

Cheers,

Ian

 

[Simply Me]

Thanks Ian...It surely helped me...

 

[IanRyder]

Good to hear you have got it sorted.

Cheers,

Ian