locked
CASE Statement for BETWEEN RRS feed

  • Question

  • I have a column as AGE in one of the table. The users want to pass one of these as parameters as '< 15', '16-30', '30-60', '61-90','> 90' since it is feed through some other system. I used the below SQL SELECT CASE statement in WHERE clause, but it is not working for between. Any idea what could be the problem?

    create table #tblTest1
    (Name varchar(10),
     Age int)
    
     INSERT INTO #tblTest1 values ('Test1',9)
     INSERT INTO #tblTest1 values ('Test2',3)
     INSERT INTO #tblTest1 values ('Test3',4)
     INSERT INTO #tblTest1 values ('Test4',52)
     INSERT INTO #tblTest1 values ('Test5',32)
     INSERT INTO #tblTest1 values ('Test6',96)
     INSERT INTO #tblTest1 values ('Test11',99)
     INSERT INTO #tblTest1 values ('Test12',23)
     INSERT INTO #tblTest1 values ('Test13',10)
     INSERT INTO #tblTest1 values ('Test14',68)
     INSERT INTO #tblTest1 values ('Test15',102)
     INSERT INTO #tblTest1 values ('Test16',68)
     INSERT INTO #tblTest1 values ('Test17',76)
     INSERT INTO #tblTest1 values ('Test18',60)
    
    
    DECLARE @Age VARCHAR(10) = '< 15'
    
    SELECT Name, Age from #tblTest1
    WHERE Age between 
          CASE WHEN @Age = '< 15'
    	    THEN 0 and 15
    	  CASE WHEN @Age = '16-30'
    	    THEN 16 and 30
    	  CASE WHEN @Age = '30-60'
    	    THEN 31 and 60
    	  CASE WHEN @Age = '60-90'
    	    THEN 60 and 90
    	  CASE WHEN @Age = '> 90'
    	    THEN 91 and 150
          END

    Thanks!

    Wednesday, January 15, 2020 4:54 PM

Answers

  • Maybe try without CASE too:

    SELECT Name, Age from #tblTest1

    WHERE

       ( @Age = '< 15'  and Age < 15)

    or ( @Age = '16-30' and Age BETWEEN 16 and 30)

    or ( @Age = '30-60' and Age BETWEEN 30 and 60)

    or ( @Age = '60-90' and Age BETWEEN 60 and 90)

    or ( @Age = '> 90'  and Age > 90)

    option (recompile)

    • Edited by Viorel_MVP Wednesday, January 15, 2020 5:13 PM
    • Marked as answer by sqldba20 Wednesday, January 15, 2020 5:43 PM
    Wednesday, January 15, 2020 5:01 PM

All replies

  • Maybe try without CASE too:

    SELECT Name, Age from #tblTest1

    WHERE

       ( @Age = '< 15'  and Age < 15)

    or ( @Age = '16-30' and Age BETWEEN 16 and 30)

    or ( @Age = '30-60' and Age BETWEEN 30 and 60)

    or ( @Age = '60-90' and Age BETWEEN 60 and 90)

    or ( @Age = '> 90'  and Age > 90)

    option (recompile)

    • Edited by Viorel_MVP Wednesday, January 15, 2020 5:13 PM
    • Marked as answer by sqldba20 Wednesday, January 15, 2020 5:43 PM
    Wednesday, January 15, 2020 5:01 PM
  • DECLARE @age varchar(10) = '> 90';
    DECLARE @minAge int;
    DECLARE @maxAge int;
    SET @minAge = CASE WHEN @age = '< 15' THEN 0 WHEN @age = '16-30' THEN 16 WHEN @age = '30-60' THEN 30 WHEN @age = '60-90' THEN 60 WHEN @age = '> 90' THEN 90 ELSE 0 END;
    SET @maxAge = CASE WHEN @age = '< 15' THEN 15 WHEN @age = '16-30' THEN 30 WHEN @age = '30-60' THEN 60 WHEN @age = '60-90' THEN 90 WHEN @age = '> 90' THEN 999 ELSE 0 END;
    SELECT * FROM #tblTest1 
    WHERE Age BETWEEN @minAge AND @maxAge;


    A Fan of SSIS, SSRS and SSAS

    Wednesday, January 15, 2020 5:43 PM
  • The first thing we need to do is correct the DDL. The first thing we need. Of course, is a key. You don't seem to know the correct syntax for an insertion statement and you had no constraints. Your data element names also violate ISO 11179 standards. Let's try this:

    CREATE TABLE Tests
    (test_name VARCHAR(10) NOT NULL PRIMARY KEY, 
     test_age INTEGER NOT NULL CHECK (test_age > 0));

    INSERT INTO Tests
    VALUES 
    ('Test1', 9), ('Test2', 3), ('Test3', 4), ('Test4', 52), 
    ('Test5', 32), ('Test6', 96), ('Test11', 99), ('Test12', 23), 
    ('Test13', 10), ('Test14', 68), ('Test15', 102), ('Test16', 68), 
    ('Test17', 76), ('Test18', 60);

    >> I have a column as test_age in one of the table. The users want to pass one of these as parameters as '< 15', '16-30', '30-60', '61-90', '> 90' since it is feed through some other system. <<

    In SQL, as well as most programming languages, a parameter is most often a scalar value and not a string to be interpreted. You're confusing a compiled language with an interpreted language. This sort of editing should be done in a front end if it's really that important to your user, and then passed to the database tier in your tiered architecture.

    >>  I used the below SQL SELECT CASE statement [sic]  WHERE clause, but it is not working for between. Any idea what could be the problem? << 

    You don't seem to understand that there is no CASE statement in SQL; it is a CASE expression and expressions by their nature return a single scalar value. Also, you might want to look up the use of the prefix "tbl_" is a really bad design practice. It mixes data and metadata and is so bad that we call it a tibble in SQL slang.

    CREATE PROCEDURE (@in_in_ltest_age INTEGER)
    AS 
    SELECT test_name, 
                CASE 
                WHEN @in_test_age BETWEEN 0 AND 15
                THEN '<= 15'
                WHEN @in_test_age BETWEEN 16 AND 30
                THEN '16 - 30'
                WHEN @in_test_age BETWEEN 31 AND 60
                THEN '31 - 60'
                WHEN @in_test_age BETWEEN 61 AND 90
                THEN '61 - 90'
                WHEN @in_test_age > 90
                THEN '91 +'
                 ELSE NULL END AS age range
    FROM Tests; 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, January 16, 2020 8:49 PM