TotalWhat Posted May 18, 2015 Share Posted May 18, 2015 (edited) Hey! I need a function that returns 'True' if the following query returns a value that is less than 45. select sum(assignmentPoints) as Points from Assignment a join Studies son a.courseCode = s.courseCode and a.assignmentName = s.assignmentName and a.sectionName = s.sectionName and pnr = '851326'where assignmentPoints > 45 I also want the query to return 'False' if that value that query returns is more than 45, if that query returns 'null' in other words. Could anyone help me out? Edited May 18, 2015 by TotalWhat Link to comment Share on other sites More sharing options...
justsomeguy Posted May 18, 2015 Share Posted May 18, 2015 How would that query return a value less than 45? It's a sum, and the where clause says to only sum values that are greater than 45. Link to comment Share on other sites More sharing options...
TotalWhat Posted May 18, 2015 Author Share Posted May 18, 2015 It does not need to return the value. It needs to return 'True' if the value is less than 45 and 'False' if the value is more than 45. That query I wrote will say if the value is lower or higher then 45. Now I need a function that uses that value to decided to return 'True' or 'False'. Link to comment Share on other sites More sharing options...
justsomeguy Posted May 18, 2015 Share Posted May 18, 2015 That query I wrote will say if the value is lower or higher then 45.Unless I'm missing something, that query is going to return the sum of all values higher than 45. That's "the value" that I think you're referring to, the sum of all point values higher than 45. If there's only 1 value there, then there's no reason to sum it, if you're summing results then it sounds like there are multiple values and you want the sum of all of them that are higher than 45.I'm a little confused about your question though. Are you trying to write a function in SQL, like a user-defined function in SQL Server, or another language? Which database are you using? Link to comment Share on other sites More sharing options...
TotalWhat Posted May 18, 2015 Author Share Posted May 18, 2015 This was solved at another forum, but I'll post the solution here as well. CREATE FUNCTION fn_FunctionName(-- Add the parameters for the function here@PNR VARCHAR(50))RETURNS BITASBEGIN-- Declare the return variable hereDECLARE @Result BIT =0 select @Result = CASE WHEN sum(assignmentPoints) > 45 THEN 1ELSE 0END as Points from Assignment a join Studies s on a.courseCode = s.courseCode and a.assignmentName = s.assignmentName and a.sectionName = s.sectionName and pnr = @PNR-- Return the result of the functionRETURN @ResultENDGO Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now