how to display value "NULL" where mark is below 50

Posted by Gow.Net under Sql Server on 9/25/2012 | Points: 10 | Views : 804 | Status : [Member] | Replies : 5
how to display value "NULL" where mark is below 50

my table :
CREATE TABLE result(
[regno] [int] NOT NULL,
[name] [nvarchar](20) NOT NULL,
[dept] [nvarchar](20) NOT NULL,
[mark1] [nvarchar](20) NOT NULL,
[mark2] [nvarchar](20) NOT NULL,
[mark3] [nvarchar](20) NOT NULL,
[mark4] [nvarchar](20) NOT NULL,
[mark5] [nvarchar](20) NOT NULL,
[mark6] [nvarchar](20) NOT NULL,
[total] [int] NOT NULL,
[status] [nvarchar](20) NOT NULL,
)


here i insert mark1,mark2,mark3,mark4,mark5,mark6 values ,i need to display value "NULL" where mark is below 50 as a temporary table

gowthaman8870226416


Responses

Posted by: Tanujad on: 9/25/2012 [Member] Starter | Points: 25

Up
0
Down
Hope this helps you --

CREATE TABLE #MarksTemporary

(
ID INT IDENTITY
, StudentID INT
, StudentName VARCHAR(100)
, Marks INT
)


INSERT INTO #MarksTemporary
(
StudentID ,
StudentName ,
Marks
)
SELECT StudentID,StudentName,NULL from StudentDetails WHERE Marks <= 25



Please mark as answer if this content helps you.

Thanks
Tanuja
(If this content helped you, Please mark it as Answer)

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vasanthmvp on: 9/25/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Gow.Net, As i am beginner in sql server. this might not be an optimized code.
here i am presenting a solution to your example, this might help you:

To script a new temporary table same as the result table. Here in result table, it should be allow nulls for the marks column.. because you want to display a null value for the marks less than (<=) 50. Here is my table schema:

Result:

CREATE TABLE [dbo].[RESULT](
[REGNO] [int] NOT NULL,
[NAME] [nvarchar](50) NOT NULL,
[DEPT] [nvarchar](50) NOT NULL,
[MARK1] [int] NULL,
[MARK2] [int] NULL,
[MARK3] [int] NULL,
[MARK4] [int] NULL,
[MARK5] [int] NULL,
[MARK6] [int] NULL,
[TOTAL] [int] NULL,
[STATUS] [nvarchar](50) NOT NULL
)

Here i have a row as

Eg: 1	VASANTH	DOT NET	50	100	100	70	50	35	500	PASS in the result table.


So, the idea is to script the same table as a temporary table and update the values where the marks are less than 50 with null value with the following query.


SELECT * INTO RESULTNEW FROM RESULT // TO CREATE A TEMPORARY TABLE(RESULTNEW) SAME AS RESULT


TO UPDATE THE VALUES

BEGIN
DECLARE

@MARK1 INT,
@MARK2 INT,
@MARK3 INT,
@MARK4 INT,
@MARK5 INT,
@MARK6 INT

SELECT @MARK1 = MARK1 FROM RESULTNEW WHERE REGNO = 1
IF(@MARK1 <= 50)
BEGIN
SET @MARK1 = NULL
END
SELECT @MARK2 = MARK2 FROM RESULTNEW WHERE REGNO = 1
IF(@MARK2 <= 50)
BEGIN
SET @MARK2 = NULL
END
SELECT @MARK3 = MARK3 FROM RESULTNEW WHERE REGNO = 1
IF(@MARK3 <= 50)
BEGIN
SET @MARK3 = NULL
END
SELECT @MARK4 = MARK4 FROM RESULTNEW WHERE REGNO = 1
IF(@MARK4 <= 50)
BEGIN
SET @MARK4 = NULL
END
SELECT @MARK5 = MARK5 FROM RESULTNEW WHERE REGNO = 1
IF(@MARK5 <= 50)
BEGIN
SET @MARK5 = NULL
END
SELECT @MARK6 = MARK6 FROM RESULTNEW WHERE REGNO = 1
IF(@MARK6 <= 50)
BEGIN
SET @MARK6 = NULL
END


UPDATE RESULTNEW SET MARK1 = @MARK1, MARK2 = @MARK2, MARK3 = @MARK3, MARK4 = @MARK4, MARK5 = @MARK5, MARK6 = @MARK6
END


After executing the query, my ResultNew table has:

1	VASANTH	DOT NET	NULL	100	100	70	NULL	NULL	500	PASS


Please correct me if there is a better procedure.
Regards,

Awesome Coding !! :)

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ranjeet_8 on: 9/27/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
try this query

SELECT CASE WHEN [mark1] < 50 THEN '' WHEN [mark1] > 49 THEN [mark1] From YourTableName


you can add more columns with CASE as per ur requirment.

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 9/27/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi

First change your table script here

[mark1] [nvarchar](20) NOT NULL,

[mark2] [nvarchar](20) NOT NULL,

[mark3] [nvarchar](20) NOT NULL,

[mark4] [nvarchar](20) NOT NULL,

[mark5] [nvarchar](20) NOT NULL,

[mark6] [nvarchar](20) NOT NULL,

remove not null becos your table allow null value after use this code here i have put null for 0 populated


Select regno,name,dept,ISNULL(Mark1,0),ISNULL(Mark2,0),ISNULL(Mark3,0),ISNULL(Mark4,0),ISNULL(Mark5,0),ISNULL(Mark6,0) from result
where Mark1<50 and Mark2<50 and Mark3<50 and Mark4<50 and Mark5<50 and Mark6<50



Mark as Answer if its helpful to you

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 9/27/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
Please mark as answer if it helpful to you.

Mark as Answer if its helpful to you

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response