on 2016 Jul 21 2:08 PM
Wrote this pre-requisite function to determine if a student has a minimum amount of hours to take a certain course. But I must exclude a repeated course in the total. Runs great in ISQL, but when calling the function, it complains "Result set not permitted in 'HoursUpperLevelFinance'" Any ideas what it is tripping on? TIA, Becky (Bradley University)
ALTER FUNCTION "DBA"."HoursUpperLevelFinance"( in @studnum unsigned integer,in @pregrades bit default 0 ) returns decimal(5,2) not deterministic begin declare @fhours decimal(5,2); set @fhours = 0; select sum ( (select top 1 credit into @fhours from dba.classes t2 where t2.studnum = t1.studnum and t2.dropdate is null and coalesce(t2.grade,'') <> 'W' and((t2.credit is not null and t2.honorpoint is not null) or @pregrades = 1) and t2.course = t1.course and t2.course_no = t1.course_no ORDER BY t2.repeatterm desc) ) as total from dba.classes t1 where t1.studnum = @studnum and t1.dropdate is null and coalesce(t1.grade,'') <> 'W' and((t1.credit is not null and t1.honorpoint is not null) or @pregrades = 1) and t1.course = 'FIN' and t1.course_no >= 300 and t1.repeatterm is null ; if @fhours is null then set @fhours = 0 end if; return @fhours end
Request clarification before answering.
You need an "INTO @fhours" in your select statement... otherwise the result set is returned to the caller. Example:
select sum ( ...[snip]... ) as total into @fhours from dba.classes t1 where t1.studnum = @studnum ...[snip]... and t1.repeatterm is null ; HTH
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
60 | |
10 | |
8 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.