1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to wirte between syntax in CASE statement THEN clause in where condition

Discussion in 'SQL PL/SQL' started by kbhaskar, Jun 13, 2013.

  1. kbhaskar

    kbhaskar Guest

    How to wirte between syntax in CASE statement THEN clause in where condition

    Ex:

    select * from tab
    where case when ' ' then date between from and to;
     
  2. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    This has no meaning, a WHERE condition is evaluated based on a boolean expression, but your CASE statement returns a data value.

    Why don't simply write something like
    Code (SQL):

    SELECT *
    FROM tab
    WHERE dataColumn BETWEEN startDate AND endDate;
     
     
  3. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi,

    I Guess u got different cases based on which u wanna use BETWEEN. Well !! Case can't be used in WHERE clause, so u do make use of gates (AND/OR) to form your conditions. Making use of gates wisely usually resolves a lot of conditional clauses.
     
  4. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260

    This is completely wrong,
    Of course you can use CASE in WHERE clause. Here is a very basic example (among many other possible examples)
    Code (SQL):

    SQL> SELECT department_id, employee_id, first_name, salary
      2    FROM hr.employees
      3    WHERE first_name LIKE 'A%' OR first_name LIKE 'S%'
      4    ORDER BY first_name ASC;

    DEPARTMENT_ID EMPLOYEE_ID FIRST_NAME           SALARY
    ------------- ----------- -------------------- ----------
           50         121 Adam               8200
           50         196 Alana              3100
           80         147 Alberto           12000
           60         103 Alexander          9000
           30         115 Alexander          3100
           50         185 Alexis             4100
           80         158 Allan              9000
           80         175 Alyssa             8800
           80         167 Amit               6200
           50         187 Anthony            3000
           50         194 Samuel             3200

    DEPARTMENT_ID EMPLOYEE_ID FIRST_NAME           SALARY
    ------------- ----------- -------------------- ----------
           50         192 Sarah              4000
           80         161 Sarath             7000
           50         123 Shanta             6500
          110         205 Shelley           12008
           30         116 Shelli             2900
           30         117 Sigal              2800
           50         138 Stephen            3200
           90         100 Steven            24000
           50         128 Steven             2200
           80         166 Sundar             6400
           80         173 Sundita            6100

    DEPARTMENT_ID EMPLOYEE_ID FIRST_NAME           SALARY
    ------------- ----------- -------------------- ----------
           40         203 Susan              6500

    23 ROWS selected.

    SQL>
    SQL>
    SQL>
    SQL> -- Now we rewrite the same query by using CASE in
    SQL> -- WHERE clause
    SQL>
    SQL> SELECT department_id, employee_id, first_name, salary
      2  FROM hr.employees
      3  WHERE 1 = CASE
      4                WHEN first_name LIKE 'A%' THEN 1
      5                WHEN first_name LIKE 'S%' THEN 1
      6                ELSE 0
      7            END
      8  ORDER BY first_name ASC;

    DEPARTMENT_ID EMPLOYEE_ID FIRST_NAME           SALARY
    ------------- ----------- -------------------- ----------
           50         121 Adam               8200
           50         196 Alana              3100
           80         147 Alberto           12000
           60         103 Alexander          9000
           30         115 Alexander          3100
           50         185 Alexis             4100
           80         158 Allan              9000
           80         175 Alyssa             8800
           80         167 Amit               6200
           50         187 Anthony            3000
           50         194 Samuel             3200

    DEPARTMENT_ID EMPLOYEE_ID FIRST_NAME           SALARY
    ------------- ----------- -------------------- ----------
           50         192 Sarah              4000
           80         161 Sarath             7000
           50         123 Shanta             6500
          110         205 Shelley           12008
           30         116 Shelli             2900
           30         117 Sigal              2800
           50         138 Stephen            3200
           90         100 Steven            24000
           50         128 Steven             2200
           80         166 Sundar             6400
           80         173 Sundita            6100

    DEPARTMENT_ID EMPLOYEE_ID FIRST_NAME           SALARY
    ------------- ----------- -------------------- ----------
           40         203 Susan              6500

    23 ROWS selected.

    SQL>

     

    Regards,
    Dariyoosh
     
  5. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi Dariyoosh,

    Thanks for sharing, i din't tried this before. Yes it's working.