Please Help Case Between Syntax

Question

0.00/5 (No votes)

See more:

Not getting proper Output
i want if Child 26 then level 3

SQL

declare  @level numeric(18)
declare  @Child numeric(18)=26
set @level=(  CASE 
 WHEN @Child < 5 THEN 0
 WHEN @Child  BETWEEN (0 AND 5)      THEN 1
 WHEN @Child BETWEEN (5 AND 25)  THEN 2
 WHEN @Child BETWEEN (25 AND 125)  THEN 3
 WHEN @Child  BETWEEN (125 AND 625)  THEN 4
 WHEN @Child  BETWEEN (625 AND 3125) THEN 5
 WHEN @Child  BETWEEN (3125 AND 15625) THEN 6
 WHEN @Child  BETWEEN (15625 AND 78125) THEN 7
 WHEN @Child BETWEEN (78125 AND 390625)THEN 8
 WHEN @Child  BETWEEN (390625 AND 1953125) THEN 9
 WHEN  @Child BETWEEN (1953125 AND 9765625) THEN 10
 END  )
 
 select @level
 select @Child

Posted 15-Mar-13 1:00am

Member-515487

Add a Solution

2 solutions

Solution 2

From your code i got 3 when child 26(as you expected). Just removed first breakete() from your tsql query inside

SQL

declare  @level numeric(18)
declare  @Child numeric(18)=26
set @level=(  CASE 
 WHEN @Child < 5 THEN 0
 WHEN @Child  BETWEEN 0 AND 5      THEN 1
 WHEN @Child BETWEEN 5 AND 25  THEN 2
 WHEN @Child BETWEEN 25 AND 125  THEN 3
 WHEN @Child  BETWEEN 125 AND 625  THEN 4
 WHEN @Child  BETWEEN 625 AND 3125 THEN 5
 WHEN @Child  BETWEEN 3125 AND 15625 THEN 6
 WHEN @Child  BETWEEN 15625 AND 78125 THEN 7
 WHEN @Child BETWEEN 78125 AND 390625 THEN 8
 WHEN @Child  BETWEEN 390625 AND 1953125 THEN 9
 WHEN  @Child BETWEEN 1953125 AND 9765625 THEN 10
 END  );

select @level
select @Child

Posted 15-Mar-13 1:25am

S. M. Ahasan Habib

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tejas Vaishnav · Accepted Answer · 2013-03-15T01:11:00

Please check this....

SQL

declare  @level numeric(18)
declare  @Child numeric(18)=26
set @level=(  
	CASE 
		 WHEN @Child < 0 THEN 0   
		 WHEN @Child  BETWEEN 0 AND 5 THEN 1
		 WHEN @Child BETWEEN 5 AND 25  THEN 2
		 WHEN @Child BETWEEN 25 AND 125  THEN 3
		 WHEN @Child  BETWEEN 125 AND 625  THEN 4
		 WHEN @Child  BETWEEN 625 AND 3125 THEN 5
		 WHEN @Child  BETWEEN 3125 AND 15625 THEN 6
		 WHEN @Child  BETWEEN 15625 AND 78125 THEN 7
		 WHEN @Child BETWEEN 78125 AND 390625THEN 8
		 WHEN @Child  BETWEEN 390625 AND 1953125 THEN 9
		 WHEN  @Child BETWEEN 1953125 AND 9765625 THEN 10
	END ) 

SELECT @level AS Level
SELECT @Child AS Child

1) your are checking @Child <5 THEN 0 and in second case your are checking @Child BETWEEN 0 AND 5 then 1 so your code always go for 0 if child is less then 5 it is not goen for second case for 1 it always return 0 for 0,1,2,3,4,5

2)remove "(" and ")" breakets from BETWEEN