multi level case in select failure

cmptrwhz · #1 (**permalink**) 03-19-10, 13:50

Hello, I am normally very good at sql but case statements are new to me and are kicking my butt. Can anyone please tell me what I am doing wrong or point me in the correct direction I need to go to find the answer please and thank you.

What I am trying to accomplish here is organize my data into columns. This statement is actually part of a large sql statement as I sum() the amounts and group by the account number. But seeing how this is the part that is failing I posted just this piece to focus on what is wrong easier.

Code:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;


CREATE TABLE `acctpayable` (
`doc` int(11) NOT NULL,
`act` int(11) NOT NULL,
`amt` float NOT NULL,
`age` date NOT NULL,
UNIQUE KEY `doc` (`doc`),
KEY `act` (`act`),
KEY `amt` (`amt`),
KEY `age` (`age`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `acctpayable` (`doc`, `act`, `amt`, `age`) VALUES
(2379941, 738, 28.59, '2010-03-08'),
(2379942, 738, 8.2, '2010-03-08'),
(2394084, 738, 16.42, '2010-05-22'),
(2372772, 775, 9.52, '2010-05-02'),
(2386032, 775, 14.38, '2010-04-14'),
(2398897, 775, 3.84, '2010-04-29'),
(2408361, 775, 1.9, '2010-07-11'),
(2425558, 775, 7.42, '2010-07-15'),
(2422020, 809, 20092, '2010-06-26'),
(2380051, 820, 1.13, '2010-06-09'),
(2384002, 820, 2.5, '2010-05-12'),
(2398900, 820, 2.5, '2010-06-29'),
(2336971, 843, 8.81, '2009-11-29');


SELECT
act,
amt,
CASE age
WHEN between '1900-01-01' and '2010-03-10' THEN '1'
WHEN between '2010-03-11' and '2010-03-25' THEN '2'
WHEN between '2010-03-26' and '2010-04-10' THEN '3'
WHEN between '2010-04-11' and '2010-04-25' THEN '4'
WHEN between '2010-04-26' and '2010-05-10' THEN '5'
WHEN between '2010-05-11' and '2010-05-25' THEN '6'
WHEN between '2010-05-26' and '2010-06-10' THEN '7'
WHEN between '2010-06-11' and '2010-06-25' THEN '8'
WHEN between '2010-06-26' and '2010-07-10' THEN '9'
WHEN between '2010-07-11' and '2010-07-25' THEN '10'
END as columnnumber
FROM
acctpayable

Results:
My results should look like this:
act, amt, columnnumber
738, 28.59, 1
738, 8.2, 1
738, 16.42, 6
775, 9.52, 5
775, 14.38, 4
775, 3.84, 5
775, 1.9, 10
775, 7.42, 10
809, 20092, 9
820, 1.13, 7
820, 2.5, 6
820, 2.5, 9
843, 8.81, 1

mike_bike_kite · #2 (**permalink**) 03-19-10, 13:59

Would you like to tell us what the error was when you ran it?

cmptrwhz · #3 (**permalink**) 03-19-10, 14:10

Sorry didn't think of that one, here is the error I receive.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'between '1900-01-01' and '2010-03-10' THEN '1' WHEN between '2010-03-11' and '20' at line 1

r937 · #4 (**permalink**) 03-19-10, 14:33

there are two forms of the CASE expression

you can use operators such as BETWEEN with only one of them

here's what you should have --

Code:

SELECT act
     , amt
     , CASE WHEN age BETWEEN '1900-01-01' AND '2010-03-10' THEN '1'
            WHEN age BETWEEN '2010-03-11' AND '2010-03-25' THEN '2'
            WHEN age BETWEEN '2010-03-26' AND '2010-04-10' THEN '3'
            WHEN age BETWEEN '2010-04-11' AND '2010-04-25' THEN '4'
            WHEN age BETWEEN '2010-04-26' AND '2010-05-10' THEN '5'
            WHEN age BETWEEN '2010-05-11' AND '2010-05-25' THEN '6'
            WHEN age BETWEEN '2010-05-26' AND '2010-06-10' THEN '7'
            WHEN age BETWEEN '2010-06-11' AND '2010-06-25' THEN '8'
            WHEN age BETWEEN '2010-06-26' AND '2010-07-10' THEN '9'
            WHEN age BETWEEN '2010-07-11' AND '2010-07-25' THEN '10'
            ELSE NULL END as columnnumber
  FROM acctpayable

cmptrwhz · #5 (**permalink**) 03-19-10, 15:45

Thank you so very very much.

cmptrwhz · #6 (**permalink**) 03-19-10, 15:52

may I ask another question of you, do you know of a good example of the two types of case statements so that I may read up on how to use both styles? Thank you.

Internet Services

Web Development

Digital Marketing

Consumer Tech