Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 How to Solve the CASE Error?

Author  Topic 

daipayan
Posting Yak Master

181 Posts
Posted - 2009-02-20 : 17:50:57
Sir/Madam,

I had the following T-SQL, please check once and tell me how to resolve the error:

--CREATE TABLE
create table MyTable
( Student varchar(10), course varchar(10), credit varchar(2)
  , gradeno int, remark varchar(10)
)
go

--INSERT SAMPLE DATA
insert MyTable select 'Steve', 'ASM01', 3, 50,0
insert MyTable select 'Steve', 'ASM01', 3, 43,0
insert MyTable select 'Bob', 'ASM01', 3, 0,0
insert MyTable select 'Bob', 'OB01', 3, 23,0
insert MyTable select 'Bob', 'OB01', 3, 59,0
insert MyTable select 'Bob', 'ASM01', 3, 100,0
insert MyTable select 'Andy', 'OB01', 3, 0,0
insert MyTable select 'Andy', 'ASM01', 3, 50,0
insert MyTable select 'Andy', 'ASM01', 3, 10,0
insert MyTable select 'Andy', 'OB01', 3, 70,0
insert MyTable select 'Andy', 'OB01', 3, 0, 'ABS'
go

--RUN QUERY
select Student, course, credit, sum(gradeno) as Grades
    , case 
                when sum(gradeno) BETWEEN 91 and 100 then 'A+'
                when sum(gradeno) BETWEEN 83 and 90 then 'A'
                when sum(gradeno) BETWEEN 75 and 82 then 'B+'
                when sum(gradeno) BETWEEN 67 and 74 then 'B'
                when sum(gradeno) BETWEEN 59 and 66 then 'C+'
                when sum(gradeno) BETWEEN 51 and 58 then 'A'
                when sum(gradeno) BETWEEN 43 and 50 then 'A'
                when sum(gradeno) BETWEEN 35 and 42 then 'A'
		when sum(gradeno) = 0 and remark = 'ABS' then 'ABS'
                when sum(gradeno) BETWEEN 0 and 34 then 'F'
                else 'NULL'
    end 'Grade'
, case 
                when sum(gradeno) BETWEEN 91 and 100 then '8'
                when sum(gradeno) BETWEEN 83 and 90 then '7'
                when sum(gradeno) BETWEEN 75 and 82 then '6'
                when sum(gradeno) BETWEEN 67 and 74 then '5'
                when sum(gradeno) BETWEEN 59 and 66 then '4'
                when sum(gradeno) BETWEEN 51 and 58 then '3'
                when sum(gradeno) BETWEEN 43 and 50 then '2'
                when sum(gradeno) BETWEEN 35 and 42 then '1'
		when sum(gradeno) = 0 and remark = 'ABS' then '0'
                when sum(gradeno) BETWEEN 0 and 34 then '0'
                else '0'
    end 'Points'
, case 
                when sum(gradeno) BETWEEN 91 and 100 then 8 * credit
                when sum(gradeno) BETWEEN 83 and 90 then 7 * credit
                when sum(gradeno) BETWEEN 75 and 82 then 6 * credit
                when sum(gradeno) BETWEEN 67 and 74 then 5 * credit
                when sum(gradeno) BETWEEN 59 and 66 then 4 * credit
                when sum(gradeno) BETWEEN 51 and 58 then 3 * credit
                when sum(gradeno) BETWEEN 43 and 50 then 2 * credit
                when sum(gradeno) BETWEEN 35 and 42 then 1 * credit
		when sum(gradeno) = 0 and remark = 'ABS' then '0'
                when sum(gradeno) BETWEEN 0 and 34 then '0'
                else '0'
    end 'Quality'
from MyTable
group by Student, course, credit, remark
go

--DROP TABLE
drop table MyTable


here, OB01 coming twice due to 'ABS' Case, I need to show the ABS as it stands for Absent means if a student is absent, he should get ZERO, what to do?
What should I do??

Daipayan

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2009-02-20 : 19:08:58
[code]select Student, course, credit, sum(gradeno) as Grades
, case
when sum(gradeno) BETWEEN 91 and 100 then 'A+'
when sum(gradeno) BETWEEN 83 and 90 then 'A'
when sum(gradeno) BETWEEN 75 and 82 then 'B+'
when sum(gradeno) BETWEEN 67 and 74 then 'B'
when sum(gradeno) BETWEEN 59 and 66 then 'C+'
when sum(gradeno) BETWEEN 51 and 58 then 'A'
when sum(gradeno) BETWEEN 43 and 50 then 'A'
when sum(gradeno) BETWEEN 35 and 42 then 'A'
when sum(gradeno) = 0 and remark = 'ABS' then '0'when sum(gradeno) BETWEEN 0 and 34 then 'F'
else 'NULL'
end 'Grade'
, case
when sum(gradeno) BETWEEN 91 and 100 then '8'
when sum(gradeno) BETWEEN 83 and 90 then '7'
when sum(gradeno) BETWEEN 75 and 82 then '6'
when sum(gradeno) BETWEEN 67 and 74 then '5'
when sum(gradeno) BETWEEN 59 and 66 then '4'
when sum(gradeno) BETWEEN 51 and 58 then '3'
when sum(gradeno) BETWEEN 43 and 50 then '2'
when sum(gradeno) BETWEEN 35 and 42 then '1'
when sum(gradeno) = 0 and remark = 'ABS' then '0'
when sum(gradeno) BETWEEN 0 and 34 then '0'
else '0'
end 'Points'
, case
when sum(gradeno) BETWEEN 91 and 100 then 8 * credit
when sum(gradeno) BETWEEN 83 and 90 then 7 * credit
when sum(gradeno) BETWEEN 75 and 82 then 6 * credit
when sum(gradeno) BETWEEN 67 and 74 then 5 * credit
when sum(gradeno) BETWEEN 59 and 66 then 4 * credit
when sum(gradeno) BETWEEN 51 and 58 then 3 * credit
when sum(gradeno) BETWEEN 43 and 50 then 2 * credit
when sum(gradeno) BETWEEN 35 and 42 then 1 * credit
when sum(gradeno) = 0 and remark = 'ABS' then '0'
when sum(gradeno) BETWEEN 0 and 34 then '0'
else '0'
end 'Quality'
from MyTable
group by Student, course, credit, remark[/code]
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2009-02-20 : 20:48:35
Shouldn't that be myTable99?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

daipayan
Posting Yak Master

181 Posts
Posted - 2009-02-21 : 03:53:47
@sodeep
I didn't got, what exactly you make changes in T-SQL??

@X002548
What you mean by MyTable99??

Daipayan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-02-21 : 04:05:14
quote:
Originally posted by daipayan

@sodeep
I didn't got, what exactly you make changes in T-SQL??

@X002548
What you mean by MyTable99??

Daipayan

see the last when condition under grade

when sum(gradeno) = 0 and remark = 'ABS' then '0'when sum(gradeno) BETWEEN 0 and 34 then 'F'
Go to Top of Page

daipayan
Posting Yak Master

181 Posts
Posted - 2009-02-21 : 04:22:31
@visakh

Same as that my T-SQl
even, giving the same error,
when I am runnin the T-SQL, the result is:
Student    course     credit Grades      Grade Points   Quality     
---------- ---------- ------ ----------- ----- ------ ----------- 
Andy       ASM01       3        60         C+     4        12 
Andy       OB01        3        70         B      5        15 
Andy       OB01        3         0         ABS    0         0 
Bob        ASM01       3       100         A+     8        24 
Bob        OB01        3        82         B+     6        18 
Steve      ASM01       3        93         A+     8        24 
-----------------------------------------------------------------
(6 row(s) affected)


You can see this Andy part, appearing twice:
Student    course     credit Grades      Grade Points   Quality     
---------- ---------- ------ ----------- ----- ------ ----------- 
Andy       OB01        3        70         B      5        15
Andy       OB01        3         0         ABS    0         0
-----------------------------------------------------------------


whereas it should come as:
Student    course     credit Grades      Grade Points   Quality     
---------- ---------- ------ ----------- ----- ------ ----------- 
Andy       OB01        3        70         B      5        15
-----------------------------------------------------------------

As he is absent in one part of course evaluation, but he appeared in other part of evaluation and got 70.

Daipayan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-02-21 : 05:02:46
modify like this and try

select Student, course, credit, sum(gradeno) as Grades
    , case 
                when sum(gradeno) BETWEEN 91 and 100 then 'A+'
                when sum(gradeno) BETWEEN 83 and 90 then 'A'
                when sum(gradeno) BETWEEN 75 and 82 then 'B+'
                when sum(gradeno) BETWEEN 67 and 74 then 'B'
                when sum(gradeno) BETWEEN 59 and 66 then 'C+'
                when sum(gradeno) BETWEEN 51 and 58 then 'A'
                when sum(gradeno) BETWEEN 43 and 50 then 'A'
                when sum(gradeno) BETWEEN 35 and 42 then 'A'
		when sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then '0'when sum(gradeno) BETWEEN 0 and 34 then 'F'
                else 'NULL'
    end 'Grade'
, case 
                when sum(gradeno) BETWEEN 91 and 100 then '8'
                when sum(gradeno) BETWEEN 83 and 90 then '7'
                when sum(gradeno) BETWEEN 75 and 82 then '6'
                when sum(gradeno) BETWEEN 67 and 74 then '5'
                when sum(gradeno) BETWEEN 59 and 66 then '4'
                when sum(gradeno) BETWEEN 51 and 58 then '3'
                when sum(gradeno) BETWEEN 43 and 50 then '2'
                when sum(gradeno) BETWEEN 35 and 42 then '1'
		when sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then '0'
                when sum(gradeno) BETWEEN 0 and 34 then '0'
                else '0'
    end 'Points'
, case 
                when sum(gradeno) BETWEEN 91 and 100 then 8 * credit
                when sum(gradeno) BETWEEN 83 and 90 then 7 * credit
                when sum(gradeno) BETWEEN 75 and 82 then 6 * credit
                when sum(gradeno) BETWEEN 67 and 74 then 5 * credit
                when sum(gradeno) BETWEEN 59 and 66 then 4 * credit
                when sum(gradeno) BETWEEN 51 and 58 then 3 * credit
                when sum(gradeno) BETWEEN 43 and 50 then 2 * credit
                when sum(gradeno) BETWEEN 35 and 42 then 1 * credit
		when sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then '0'
                when sum(gradeno) BETWEEN 0 and 34 then '0'
                else '0'
    end 'Quality'
from MyTable
group by Student, course, credit, remark
Go to Top of Page

daipayan
Posting Yak Master

181 Posts
Posted - 2009-02-21 : 05:59:04
@visakh
Thanks a lot, it's working now....

Sir, one more favour!
I want this whenever I update data in MyTable, then through this T-SQL, the Student, course,credit,grades,grade,points,quality will automatically updates in one table!
Is this possible or not through some procedure or function??

Daipayan
Go to Top of Page

bklr
Master Smack Fu Yak Hacker

1693 Posts
Posted - 2009-02-21 : 06:02:10
then use a trigger for this
Go to Top of Page

daipayan
Posting Yak Master

181 Posts
Posted - 2009-02-21 : 06:05:17
dis
quote:
Originally posted by bklr

then use a trigger for this


How should I use trigger??
Will you please guide me?

Daipayan
Go to Top of Page

bklr
Master Smack Fu Yak Hacker

1693 Posts
Posted - 2009-02-21 : 06:19:59
sorry daipayan,
u can use a storedprocedure for this than trigger.....
Go to Top of Page

bklr
Master Smack Fu Yak Hacker

1693 Posts
Posted - 2009-02-21 : 06:23:05
[code]just a sample sp see this and change ur code according to ur requirement
CREATE PROC usp_sampproc
( parameters
)
AS
SET NOCOUNT ON
BEGIN

update mytable
set col=..,
where if any condition

update anothertable
set Student= i.student,
course= i.course,
credit = i credit,grades = i.grades,grade = i.grades ,points = i.points ,quality = i.quality
from mytable i
END
SET NOCOUNT OFF
[/code]
Go to Top of Page

daipayan
Posting Yak Master

181 Posts
Posted - 2009-02-21 : 06:25:25
quote:
Originally posted by bklr

sorry daipayan,
u can use a storedprocedure for this than trigger.....


How should I use the storedprocedure??
Am not getting, how to do it?


Daipayan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-02-22 : 01:22:53
try like this

CREATE TRIGGER UpdateDetails
ON MyTable
AFTER UPDATE
AS
BEGIN
INERT INTO DestinationTable 
select Student, course, credit, sum(gradeno) as Grades
    , case 
                when sum(gradeno) BETWEEN 91 and 100 then 'A+'
                when sum(gradeno) BETWEEN 83 and 90 then 'A'
                when sum(gradeno) BETWEEN 75 and 82 then 'B+'
                when sum(gradeno) BETWEEN 67 and 74 then 'B'
                when sum(gradeno) BETWEEN 59 and 66 then 'C+'
                when sum(gradeno) BETWEEN 51 and 58 then 'A'
                when sum(gradeno) BETWEEN 43 and 50 then 'A'
                when sum(gradeno) BETWEEN 35 and 42 then 'A'
		when sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then '0'when sum(gradeno) BETWEEN 0 and 34 then 'F'
                else 'NULL'
    end 'Grade'
, case 
                when sum(gradeno) BETWEEN 91 and 100 then '8'
                when sum(gradeno) BETWEEN 83 and 90 then '7'
                when sum(gradeno) BETWEEN 75 and 82 then '6'
                when sum(gradeno) BETWEEN 67 and 74 then '5'
                when sum(gradeno) BETWEEN 59 and 66 then '4'
                when sum(gradeno) BETWEEN 51 and 58 then '3'
                when sum(gradeno) BETWEEN 43 and 50 then '2'
                when sum(gradeno) BETWEEN 35 and 42 then '1'
		when sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then '0'
                when sum(gradeno) BETWEEN 0 and 34 then '0'
                else '0'
    end 'Points'
, case 
                when sum(gradeno) BETWEEN 91 and 100 then 8 * credit
                when sum(gradeno) BETWEEN 83 and 90 then 7 * credit
                when sum(gradeno) BETWEEN 75 and 82 then 6 * credit
                when sum(gradeno) BETWEEN 67 and 74 then 5 * credit
                when sum(gradeno) BETWEEN 59 and 66 then 4 * credit
                when sum(gradeno) BETWEEN 51 and 58 then 3 * credit
                when sum(gradeno) BETWEEN 43 and 50 then 2 * credit
                when sum(gradeno) BETWEEN 35 and 42 then 1 * credit
		when sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then '0'
                when sum(gradeno) BETWEEN 0 and 34 then '0'
                else '0'
    end 'Quality'
from INSERTED
group by Student, course, credit

END

destination table is table to which details are to be added
Go to Top of Page

daipayan
Posting Yak Master

181 Posts
Posted - 2009-02-22 : 04:43:32
@visakh16

As you said, first I created a table:
CREATE TABLE DestinationTable
( Student varchar(10), course varchar(10), credit varchar(2), Grades int,
  Grade varchar(10), Points int, Quality int 
)
GO


Then I run TRIGGER PART:
CREATE TRIGGER UpdateDetails
ON MyTable
AFTER UPDATE
AS
BEGIN
INSERT INTO DestinationTable 
select Student, course, credit, sum(gradeno) as Grades
    , case 
                when sum(gradeno) BETWEEN 91 and 100 then 'A+'
                when sum(gradeno) BETWEEN 83 and 90 then 'A'
                when sum(gradeno) BETWEEN 75 and 82 then 'B+'
                when sum(gradeno) BETWEEN 67 and 74 then 'B'
                when sum(gradeno) BETWEEN 59 and 66 then 'C+'
                when sum(gradeno) BETWEEN 51 and 58 then 'A'
                when sum(gradeno) BETWEEN 43 and 50 then 'A'
                when sum(gradeno) BETWEEN 35 and 42 then 'A'
		when sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then '0'
		when sum(gradeno) BETWEEN 0 and 34 then 'F'
                else 'NULL'
    end 'Grade'
, case 
                when sum(gradeno) BETWEEN 91 and 100 then '8'
                when sum(gradeno) BETWEEN 83 and 90 then '7'
                when sum(gradeno) BETWEEN 75 and 82 then '6'
                when sum(gradeno) BETWEEN 67 and 74 then '5'
                when sum(gradeno) BETWEEN 59 and 66 then '4'
                when sum(gradeno) BETWEEN 51 and 58 then '3'
                when sum(gradeno) BETWEEN 43 and 50 then '2'
                when sum(gradeno) BETWEEN 35 and 42 then '1'
		when sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then '0'
                when sum(gradeno) BETWEEN 0 and 34 then '0'
                else '0'
    end 'Points'
, case 
                when sum(gradeno) BETWEEN 91 and 100 then 8 * credit
                when sum(gradeno) BETWEEN 83 and 90 then 7 * credit
                when sum(gradeno) BETWEEN 75 and 82 then 6 * credit
                when sum(gradeno) BETWEEN 67 and 74 then 5 * credit
                when sum(gradeno) BETWEEN 59 and 66 then 4 * credit
                when sum(gradeno) BETWEEN 51 and 58 then 3 * credit
                when sum(gradeno) BETWEEN 43 and 50 then 2 * credit
                when sum(gradeno) BETWEEN 35 and 42 then 1 * credit
		when sum(gradeno) = 0 and sum(case when remark = 'ABS' then 1 else 0 end)>0 then '0'
                when sum(gradeno) BETWEEN 0 and 34 then '0'
                else '0'
    end 'Quality'
from MyTable
group by Student, course, credit
END


But nothing in automatically updating in DestinationTable?
Am I doing something wrong??

Daipayan
Go to Top of Page

daipayan
Posting Yak Master

181 Posts
Posted - 2009-03-02 : 17:14:09
Why this trigger not automatically updating DestinationTable??

Daipayan
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site