Why Doesn't "Case" with "When > 2" Work

Why doesn't case with when 2 work?

An if statement would probably be more fitting for your code, since you don't have a definitive range/value, but rather just a greater-than:

if ARGV.length == 0
  abort "Error 1"
elsif ARGV.length > 2
  abort "Error 2"
end

SQL: Why does 'Case When' won't work in my code?

You are confusing the two types of cases. You want the version with separate conditions:

select (case when (b.stars >= 2.0 and b.stars <= 3.0) then '2-3'
             when (b.stars >= 4.0) then '4-5'
             else 'none'
        end) as stars_group

If you are just using equality, you can use a simple case expression -- but the comparisons need to strict equality:

select (case trunc(b.stars)
             when 2 then 'Two'
             when 3 then 'Three'
             else 'none'
        end) as stars_group

With inequalities, you need a searched case where each where clause is evaluated to determine the first then that is returned. The searched case has no expression between case and where.

How do I do multiple CASE WHEN conditions using SQL Server 2008?

There are three formats of case expression. You can do CASE with many WHEN as;

CASE  WHEN Col1 = 1 OR Col3 = 1  THEN 1 
      WHEN Col1 = 2 THEN 2
      ...
      ELSE 0 END as Qty

Or a Simple CASE expression

CASE Col1 WHEN 1 THEN 11 WHEN 2 THEN 21 ELSE 13 END

Or CASE within CASE as;

CASE  WHEN Col1 < 2 THEN  
                    CASE Col2 WHEN 'X' THEN 10 ELSE 11 END
      WHEN Col1 = 2 THEN 2
      ...
      ELSE 0 END as Qty

MySQL CASE for value range doesn't work but nested IF's do?

You were close but had some syntax errors. Do this instead:

CASE 
    WHEN total_hours >= 1 AND total_hours <= 50 THEN
        'Bronze'
    WHEN total_hours >= 51 AND total_hours <= 125 THEN
        'Silver'
    WHEN total_hours >= 126 AND total_hours <= 249 THEN
        'Gold'
    WHEN total_hours >= 250 THEN
        'Platinum'
    ELSE
        'Less than 1 hour'
END AS award

Sample simplified SQL Fiddle

Java: Why Doesn't My While Statement Work With My Case 2?

Change

    uone = input.nextInt();
    utwo = input.nextInt();
    switch (one){

To

    int choice = input.nextInt();

    switch (choice)

CASE IN statement with multiple values

Yes. You need to use the "Searched" form rather than the "Simple" form of the CASE expression

SELECT CASE
         WHEN c.Number IN ( '1121231', '31242323' ) THEN 1
         WHEN c.Number IN ( '234523', '2342423' ) THEN 2
       END AS Test
FROM   tblClient c  

Why doesn't this MySQL CASE query work?

Your syntax is a little strange. You don't need the = 1 in the LIKE case, the second condition has an invalid = 2, and since both are conditions of the same statement, don't repeat CASE -- instead begin the second condition with WHEN.

It is also recommended to put in an ELSE case to match all other rows deterministically. Below, I inserted ELSE 3, which sorts all other non-matched rows after the two matching conditions.

Finally, the whole construct should end with an END keyword.

SELECT * FROM `cronjob_reloaded` 
WHERE 
`carid` LIKE '%bmw%'
OR 
`age` BETWEEN '10' AND '15'
ORDER BY 
  CASE
    WHEN `carid` LIKE '%bmw%' THEN 1  
    WHEN `age` BETWEEN '10' AND '15'  THEN 2
    /* Advisable to add an ELSE condition to catch all other rows */
    ELSE 3
  END

---

Related Topics