Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Unanswered: multi level case in select failure---SOLVED

    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
    Last edited by cmptrwhz; 03-19-10 at 18:15. Reason: solved

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Would you like to tell us what the error was when you ran it?

  3. #3
    Join Date
    Mar 2010
    Posts
    4
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2010
    Posts
    4
    Thank you so very very much.

  6. #6
    Join Date
    Mar 2010
    Posts
    4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •