Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80

    Question Unanswered: CASE length limit?

    Is there some sort of a limit to the length of a CASE expression? If I run the following using SOURCE, it only returns region values for the first dozen results or so. The rest all come back with the region values of NULL, and that's not at all what the data looks like.

    Here's the code:

    Code:
    SELECT @regionBC := regionID
    FROM   region
    WHERE  region = 'BC';
    
    SELECT @regionAB := regionID
    FROM   region
    WHERE  region = 'AB';
    
    SELECT @regionSK := regionID
    FROM   region
    WHERE  region = 'SK';
    
    SELECT @regionON := regionID
    FROM   region
    WHERE  region = 'ON';
    
    SELECT @regionNB := regionID
    FROM   region
    WHERE  region = 'NB';
    
    SELECT @regionWA := regionID
    FROM   region
    WHERE  region = 'WA';
    
    SELECT DISTINCT 
     city, 
     CASE 
      WHEN city IN ('Edmonton') THEN @regionAB
      WHEN city IN ('Fredericton') THEN @regionNB
      WHEN city IN ('Waterloo', 'Essex', 'Ottawa',
                    'Petawawa', 'Nipissing') THEN @regionON
      WHEN city IN ('Regina') THEN @regionSK
      WHEN city IN ('Seattle', 'Poulsbo', 'Port Angeles')
                    THEN @regionWA  
      WHEN UCASE(city) 
       IN ('Alert Bay', 'Anglemont', 'Brentwood Bay',
           'Burnaby', 'Campbell River', 
           'Central Saanich', 'Cobble Hill', 'Comox',
           'Courtenay', 'Cowichan Bay', 'Cranbrook', 'Creston',
           'Crofton', 'DUNCAN', 'Edmonton', 
           'Galiano Island', 'Kamloops', 
           'Ladysmith', 'Lake Cowichan', 'Logan Lake',
           'Malahat', 'Mill Bay', 'Nanaimo', 'New Westminster',
           'North Saanich', 'Parksville', 
           'Pender Island', 'Port Alberni', 
           'Port McNeill',
           'Qualicum Beach', 'Saanichton',
           'Sayward', 'Shawnigan Lake', 'Sidney',
           'Sooke', 'Trail', 'Vancouver', 'Victoria',
           'Youbou', 'Zeballos') THEN @regionBC
     END AS regionID          
     FROM members;
    And here's the first bit of the result:

    -> END AS regionID
    -> FROM members;
    +------------------+----------+
    | city | regionID |
    +------------------+----------+
    | Waterloo | 1 |
    | Victoria | 2 |
    | SIDNEY | 2 |
    | SOOKE | 2 |
    | KAMLOOPS | 2 |
    | QUALICUM BEACH | 2 |
    | NORTH SAANICH | 2 |
    | PARKSVILLE | 2 |
    | Saanichton | 2 |
    | Shawnigan Lake | 2 |
    | REGINA | 3 |
    | BRENTWOOD BAY | 2 |
    | COURTENAY | 2 |
    | COBBLE HILL | 2 |
    | NEW WESTMINSTER | 2 |
    | SALT SPRING ISLE | NULL |
    | VICTORIA | NULL |
    | GALIANO | NULL |
    | SALT SPRING IS | NULL |

    ...more nulls follow.
    Last edited by bcrockett; 02-18-03 at 17:11.

  2. #2
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80

    Angry

    I figured it out - I forgot to put an ELSE item at the end of the CASE.

    Bradley

Posting Permissions

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