Solved

CASE STATEMENT RETURNING NULLS

Posted on 2007-12-05
17
309 Views
Last Modified: 2008-02-01
I am writing this function and when i call it with a select statement I am getting nulls. Any ideas?

ALTER FUNCTION [dbo].[fn_deductible]
(
      -- Add the parameters for the function here
            @DEDUCTIBLEAMT VARCHAR(7)
)
RETURNS varchar (1)
AS
BEGIN
-- Declare the return variable here

DECLARE @DEDUCTIBLE varchar(1)
DECLARE @DEDUCTIBLETEMP varchar(7)


-- Add the T-SQL statements to compute the return value here

SET @DEDUCTIBLETEMP = CAST(@DEDUCTIBLEAMT AS NUMERIC (7,0))
set @DEDUCTIBLE = case @DEDUCTIBLETEMP


when '> 100 and <= 250' then '4'
when  '> 500 and  <= 750' then '6'
when  '> 750 and  <= 1000' then '7'
when  '> 1000 and  <= 2500' then '8'
when  '> 2500 and  <= 5000' then '9'
when  '> 5000 and  <= 10000' then 'A'
when  '> 10000 and  <= 25000' then 'B'
when  '> 25000 and  <= 50000' then 'C'
when  '> 50000 and  <= 75000' then 'D'
when  '> 75000 and  <= 100000' then 'E'
when  '> 100000 and  <= 200000' then 'F'
when  '> 200000 and  <= 250000' then 'G'
when  '> 250000 and  <= 300000' then 'H'
when  '> 300000 and  <= 400000' then 'I'
when  '> 400000 and  <= 500000' then 'J'
when  '> 500000' then 'k'
end

      RETURN @DEDUCTIBLE

END
0
Comment
Question by:jtsoos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +3
17 Comments
 
LVL 9

Expert Comment

by:CCongdon
ID: 20413466
Is it possible that DEDUCTUIBLETEMP is coming out with a valuie less than or equal to 100? That would give you a NULL result.

Try adding ELSE '!!' after when  '> 500000' then 'k' and see if you get any !! in your results. If so, then you are getting some values less than 101 OR you are getting NULL values as inputs.
0
 
LVL 9

Expert Comment

by:CCongdon
ID: 20413471
Sorry make that a single exclamation point. I just noticed that your sp returns a single character.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20414056
this should do it.
ALTER FUNCTION [dbo].[fn_deductible] 
(
      -- Add the parameters for the function here
      @DEDUCTIBLEAMT VARCHAR(7)
)
RETURNS varchar (1)
AS
BEGIN
-- Declare the return variable here 
DECLARE @DEDUCTIBLE varchar(1)
DECLARE @DEDUCTIBLETEMP NUMERIC (7,0) 

-- Add the T-SQL statements to compute the return value here 
SET @DEDUCTIBLETEMP = CAST(@DEDUCTIBLEAMT AS NUMERIC (7,0))
set @DEDUCTIBLE = case 
when @DEDUCTIBLETEMP > 100 and @DEDUCTIBLETEMP <= 250 then '4'
when @DEDUCTIBLETEMP > 500 and @DEDUCTIBLETEMP <= 750 then '6'
when @DEDUCTIBLETEMP > 750 and @DEDUCTIBLETEMP <= 1000 then '7'
when @DEDUCTIBLETEMP > 1000 and @DEDUCTIBLETEMP <= 2500 then '8'
when @DEDUCTIBLETEMP > 2500 and @DEDUCTIBLETEMP <= 5000 then '9'
when @DEDUCTIBLETEMP  > 5000 and @DEDUCTIBLETEMP  <= 10000 then 'A'
when @DEDUCTIBLETEMP  > 10000 and  @DEDUCTIBLETEMP <= 25000 then 'B'
when @DEDUCTIBLETEMP  > 25000 and  @DEDUCTIBLETEMP <= 50000 then 'C'
when @DEDUCTIBLETEMP  > 50000 and  @DEDUCTIBLETEMP <= 75000 then 'D'
when @DEDUCTIBLETEMP  > 75000 and  @DEDUCTIBLETEMP <= 100000 then 'E'
when @DEDUCTIBLETEMP  > 100000 and @DEDUCTIBLETEMP  <= 200000 then 'F'
when  @DEDUCTIBLETEMP > 200000 and @DEDUCTIBLETEMP  <= 250000 then 'G'
when  @DEDUCTIBLETEMP > 250000 and  @DEDUCTIBLETEMP <= 300000 then 'H'
when  @DEDUCTIBLETEMP > 300000 and  @DEDUCTIBLETEMP <= 400000 then 'I'
when  @DEDUCTIBLETEMP > 400000 and  @DEDUCTIBLETEMP <= 500000 then 'J'
when  @DEDUCTIBLETEMP > 500000 then 'k'
end 
      RETURN @DEDUCTIBLE 
END

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jtsoos
ID: 20414568
this is what wound up working

ALTER FUNCTION [dbo].[fn_deductible2]
(
      -- Add the parameters for the function here
      @DEDUCTIBLEAMT VARCHAR(7)
)
RETURNS varchar (1)
AS
BEGIN
      -- Declare the return variable here
      DECLARE @DEDUCTIBLECODE varchar(1)


      -- Add the T-SQL statements to compute the return value here
select @DeductibleCode = CASE

WHEN cast(@DeductibleAmt as int) BETWEEN 0 AND 50 THEN '2'
WHEN cast(@DeductibleAmt as int) BETWEEN 51 AND 100 THEN '3'
WHEN cast(@DeductibleAmt as int) BETWEEN 101 AND 250 THEN '4'
WHEN cast(@DeductibleAmt as int) BETWEEN 251 AND 500 THEN '5'
WHEN cast(@DeductibleAmt as int) BETWEEN 501 AND 750 THEN '6'
WHEN cast(@DeductibleAmt as int) BETWEEN 751 AND 1000 THEN '7'
WHEN cast(@DeductibleAmt as int) BETWEEN 1001 AND 2500 THEN '8'
WHEN cast(@DeductibleAmt as int) BETWEEN 2501 AND 5000 THEN '9'
WHEN cast(@DeductibleAmt as int) BETWEEN 5001 AND 10000 THEN 'A'
WHEN cast(@DeductibleAmt as int) BETWEEN 10001 AND 25000 THEN 'B'
WHEN cast(@DeductibleAmt as int) BETWEEN 25001 AND 50000 THEN 'C'
WHEN cast(@DeductibleAmt as int) BETWEEN 50001 AND 75000 THEN 'D'
WHEN cast(@DeductibleAmt as int) BETWEEN 75001 AND 100000 THEN 'E'
WHEN cast(@DeductibleAmt as int) BETWEEN 100001 AND 200000 THEN 'F'
WHEN cast(@DeductibleAmt as int) BETWEEN 200001 AND 250000 THEN 'G'
WHEN cast(@DeductibleAmt as int) BETWEEN 250001 AND 300000 THEN 'H'
WHEN cast(@DeductibleAmt as int) BETWEEN 300001 AND 400000 THEN 'I'
WHEN cast(@DeductibleAmt as int) BETWEEN 400001 AND 500000 THEN 'J'
WHEN CAST(@DEDUCTIBLEAMT AS INT) > 500000 THEN 'K'

ELSE '99'

END


      -- Return the result of the function
      RETURN @DEDUCTIBLECODE

END
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20414607
well...

ELSE '99'

is incompatible with

RETURNS varchar (1)


you need to change to

RETURNS varchar (2)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20414613
another question:
why is your function parameter data types varchar(7) and not INT directly?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20416643
I would propose something much simpler and faster, if you give me 2 minutes
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20416659
The gains:

Casting is done only once

Testing does not have to revisit the "BETWEEN 2" conditions. Remember that CASE takes only the first match.

Nulls and -ve (the only two cases caught by ELSE) are now handled as X

Yes, Angel is right. Your ELSE '99'  will cause '9' to be returned, and it will confuse the hell out of you!  (same as 2501-5000)

Your boundary condition

WHEN cast(@DeductibleAmt as int) BETWEEN 400001 AND 500000 THEN 'J'
WHEN CAST(@DEDUCTIBLEAMT AS INT) > 500000 THEN 'K'

Misses when it's 500000 exactly.
ALTER FUNCTION [dbo].[fn_deductible2]
(
      -- Add the parameters for the function here
      @DEDUCTIBLEAMT VARCHAR(7)
)
RETURNS varchar (1)
AS
BEGIN
 
-- Declare the return variable here
DECLARE @DEDUCTIBLECODE varchar(1)
DECLARE @DeductibleAmtINT int
SET @DeductibleAmtINT = IsNull(Cast(@DeductibleAmt as Int), -1)
 
-- Add the T-SQL statements to compute the return value here
select @DeductibleCode = CASE
WHEN @DeductibleAmtInt < 0 THEN 'X'
WHEN @DeductibleAmtInt < 50 THEN '2'
WHEN @DeductibleAmtInt < 100 THEN '3'
WHEN @DeductibleAmtInt < 250 THEN '4'
WHEN @DeductibleAmtInt < 500 THEN '5'
WHEN @DeductibleAmtInt < 750 THEN '6'
WHEN @DeductibleAmtInt < 1000 THEN '7'
WHEN @DeductibleAmtInt < 2500 THEN '8'
WHEN @DeductibleAmtInt < 5000 THEN '9'
WHEN @DeductibleAmtInt < 10000 THEN 'A'
WHEN @DeductibleAmtInt < 25000 THEN 'B'
WHEN @DeductibleAmtInt < 50000 THEN 'C'
WHEN @DeductibleAmtInt < 75000 THEN 'D'
WHEN @DeductibleAmtInt < 100000 THEN 'E'
WHEN @DeductibleAmtInt < 200000 THEN 'F'
WHEN @DeductibleAmtInt < 250000 THEN 'G'
WHEN @DeductibleAmtInt < 300000 THEN 'H'
WHEN @DeductibleAmtInt < 400000 THEN 'I'
WHEN @DeductibleAmtInt < 500000 THEN 'J'
ELSE 'K'
END
 
-- Return the result of the function
RETURN @DEDUCTIBLECODE
 
END

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20417563
@amitchie:

>Your boundary condition
>WHEN cast(@DeductibleAmt as int) BETWEEN 400001 AND 500000 THEN 'J'
>WHEN CAST(@DEDUCTIBLEAMT AS INT) > 500000 THEN 'K'

AFAIK, and I think you have to agree, that BETWEEN is inclusive the borders?! ...

for the rest, I do agree with your suggestion to be a more complete and efficient coding...
except that you need to replace the < by <= for all the checks except the first one against 0

ALTER FUNCTION [dbo].[fn_deductible2]
(
      -- Add the parameters for the function here
      @DEDUCTIBLEAMT VARCHAR(7)
)
RETURNS varchar (1)
AS
BEGIN
 
-- Declare the return variable here
DECLARE @DEDUCTIBLECODE varchar(1)
DECLARE @DeductibleAmtINT int
SET @DeductibleAmtINT = IsNull(Cast(@DeductibleAmt as Int), -1)
 
-- Add the T-SQL statements to compute the return value here
select @DeductibleCode = CASE
WHEN @DeductibleAmtInt < 0 THEN 'X'
WHEN @DeductibleAmtInt <= 50 THEN '2'
WHEN @DeductibleAmtInt <= 100 THEN '3'
WHEN @DeductibleAmtInt <= 250 THEN '4'
WHEN @DeductibleAmtInt <= 500 THEN '5'
WHEN @DeductibleAmtInt <= 750 THEN '6'
WHEN @DeductibleAmtInt <= 1000 THEN '7'
WHEN @DeductibleAmtInt <= 2500 THEN '8'
WHEN @DeductibleAmtInt <= 5000 THEN '9'
WHEN @DeductibleAmtInt <= 10000 THEN 'A'
WHEN @DeductibleAmtInt <= 25000 THEN 'B'
WHEN @DeductibleAmtInt <= 50000 THEN 'C'
WHEN @DeductibleAmtInt <= 75000 THEN 'D'
WHEN @DeductibleAmtInt <= 100000 THEN 'E'
WHEN @DeductibleAmtInt <= 200000 THEN 'F'
WHEN @DeductibleAmtInt <= 250000 THEN 'G'
WHEN @DeductibleAmtInt <= 300000 THEN 'H'
WHEN @DeductibleAmtInt <= 400000 THEN 'I'
WHEN @DeductibleAmtInt <= 500000 THEN 'J'
ELSE 'K'
END
 
-- Return the result of the function
RETURN @DEDUCTIBLECODE
 
END

I want to make a remark about the flexibility part...
I would consider creating a table like this:

tbl_DeductiableAmountCode
UpperBorder int    Code char(1)
-1           X
50          2
100        3
....
2000000000  K
etc...

and your function would take that table into account, and any changes in the codings could be done by just modifying the table...


ALTER FUNCTION [dbo].[fn_deductible2]
(
      -- Add the parameters for the function here
      @DEDUCTIBLEAMT INT
)
RETURNS varchar (1)
AS
BEGIN
 
-- Declare the return variable here
DECLARE @DEDUCTIBLECODE varchar(1)
DECLARE @DeductibleAmtINT int
SET @DeductibleAmtINT = IsNull(@DeductibleAmt, -1)
 
-- Add the T-SQL statements to compute the return value here
SELECT top 1 @DeductibleCode = Code
FROM tbl_DeductiableAmountCode
WHERE @DeductibleAmtInt <= UpperBorder
ORDER BY UpperBorder DESC

 
-- Return the result of the function
RETURN @DEDUCTIBLECODE
 
END

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20417569
good point. my eyes are probably seeing BETWEEN A AND B but fried brain is thinking >A and <B ...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20559166
I have to disagree with that recommendation.

the question code reads like this:
 case @DEDUCTIBLETEMP when '> 100 and <= 250' then '4'

so, the variable was compared litterally with the string '> 100 and <= 250'
which cannot match on any of the lines.

altough the remark about the check " <= 100 " was missing in the list, that was not main problem, but the syntax problem that the code had to read instead like this:

case when @DEDUCTIBLETEMP  > 100 and  @DEDUCTIBLETEMP  <= 250 then '4'


to back up that, see:
http://www.experts-exchange.com/Database/Reporting_/Q_23003599.html#a20414568
this is what wound up working ... <...>

after which the questioner did not show up again.

so, if you want to give points to a single expert, that would be me...
however, I would like to recommend a point split between CCongdon , imitchie and myself.
both experts have added valuable ideas to this question
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20559177
Give all the points to angel.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 20570397
angel - You are correct.  I mistakenlyassumed the code was working with th eproblem being solved by the else.  The code wasn't working because the ' ' around the conditions.

mlmcc
0
 
LVL 9

Expert Comment

by:CCongdon
ID: 20574668
Yes, please gives points to Angelll and not me. I simply made a quick 'grasping at straws' idea just to give the questioner something to try. Angelll certainly did more work on this question than I have.
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20592605
Forced accept.

Computer101
EE Admin
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query to calculate avaerage 21 57
How to count the days a record spends in a step 21 56
SQL Syntax Grouping Sum question 7 36
compare date to getdate() 8 17
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question