select statement, CASE ...AS usage


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: select statement, CASE ...AS usage

  1. #1
    Matt Offutt Guest

    select statement, CASE ...AS usage


    In SQL Server 7 I'd like to use a 'column' that is created in a Select statement
    (REGION)in the Group by, however I am getting an error indicating an invalid
    column name.

    Any suggestions?

    Thanks for your help.
    -------------------------------------------

    select GEOGRAPHY, DATE, Count(*) AS REC_COUNT,
    CASE WHEN GEOGRAPHY = 'CANADA' THEN 'NA'
    WHEN GEOGRAPHY = 'UNITED STATES' THEN 'NA'
    WHEN GEOGRAPHY = 'KOREA' THEN 'AP'
    WHEN GEOGRAPHY = 'CHINA' THEN 'AP'
    END AS REGION
    FROM table_1
    group by REGION, GEOGRAPHY, DATE

  2. #2
    VSLAV Guest

    Re: select statement, CASE ...AS usage


    "Matt Offutt" <matt_offutt@hp.com> wrote:
    >
    >In SQL Server 7 I'd like to use a 'column' that is created in a Select statement
    >(REGION)in the Group by, however I am getting an error indicating an invalid
    >column name.
    >
    >Any suggestions?
    >
    >Thanks for your help.
    >-------------------------------------------
    >
    >select GEOGRA



    You can't use column REGION in this Select statement. There are two ways
    how to avoid it:

    First way - to use CASE function in GROUP BY:
    select GEOGRAPHY, DATE, Count(*) AS REC_COUNT,
    CASE WHEN GEOGRAPHY = 'CANADA' THEN 'NA'
    WHEN GEOGRAPHY = 'UNITED STATES' THEN 'NA'
    WHEN GEOGRAPHY = 'KOREA' THEN 'AP'
    WHEN GEOGRAPHY = 'CHINA' THEN 'AP'
    END AS REGION
    FROM table_1
    group by CASE WHEN GEOGRAPHY = 'CANADA' THEN 'NA'
    WHEN GEOGRAPHY = 'UNITED STATES' THEN 'NA'
    WHEN GEOGRAPHY = 'KOREA' THEN 'AP'
    WHEN GEOGRAPHY = 'CHINA' THEN 'AP'
    END, GEOGRAPHY, DATE

    Second way - to use subquery:
    select GEOGRAPHY, DATE, Count(*) AS REC_COUNT, REGION
    from (
    select GEOGRAPHY, DATE,
    CASE WHEN GEOGRAPHY = 'CANADA' THEN 'NA'
    WHEN GEOGRAPHY = 'UNITED STATES' THEN 'NA'
    WHEN GEOGRAPHY = 'KOREA' THEN 'AP'
    WHEN GEOGRAPHY = 'CHINA' THEN 'AP'
    END AS REGION
    FROM table_1 ) as x
    group by x.REGION, x.GEOGRAPHY, x.DATE

    Thanks,
    VSLAV



  3. #3
    Manos Kelaiditis Guest

    Re: select statement, CASE ...AS usage


    Try to rewrite the query like that:

    SELECT GEOGRAPHY, DATE, COUNT(*) AS REC_COUNT, REGION = CASE GEOGRAPHY
    WHEN 'CANADA' THEN 'NA'
    WHEN 'UNITED STATES' THEN
    'NA'
    WHEN 'KOREA' THEN 'AP'
    WHEN 'CHINA' THEN 'AP'
    END
    FROM table_1
    GROUP BY GEOGRAPHY, DATE

    Anyway, you can not Group By the REGION column, you must use the name of
    the column that follows the CASE keyword. If you use the column GEOGRAPHY
    twice the grouping will occur for the first instance of the column.
    Just for the record, using CASE on a query is a bad practice. The SELECT
    statement is a SET oriented operation that you enforce to use procedural
    logic.. Check out to see if the indexes are used...


    "Matt Offutt" <matt_offutt@hp.com> wrote:
    >
    >In SQL Server 7 I'd like to use a 'column' that is created in a Select statement
    >(REGION)in the Group by, however I am getting an error indicating an invalid
    >column name.
    >
    >Any suggestions?
    >
    >Thanks for your help.
    >-------------------------------------------
    >
    >select GEOGRAPHY, DATE, Count(*) AS REC_COUNT,
    > CASE WHEN GEOGRAPHY = 'CANADA' THEN 'NA'
    > WHEN GEOGRAPHY = 'UNITED STATES' THEN 'NA'
    > WHEN GEOGRAPHY = 'KOREA' THEN 'AP'
    > WHEN GEOGRAPHY = 'CHINA' THEN 'AP'
    > END AS REGION
    >FROM table_1
    >group by REGION, GEOGRAPHY, DATE



  4. #4
    John Perry Guest

    Re: select statement, CASE ...AS usage


    Matt,

    GROUP BY likes to have the exact syntax in your select statement, but without
    the reference to what you've named it. I don't have a server to test this
    but something like this should work:

    select GEOGRAPHY, DATE, Count(*) AS REC_COUNT,
    CASE WHEN GEOGRAPHY = 'CANADA' THEN 'NA'
    WHEN GEOGRAPHY = 'UNITED STATES' THEN 'NA'
    WHEN GEOGRAPHY = 'KOREA' THEN 'AP'
    WHEN GEOGRAPHY = 'CHINA' THEN 'AP'
    END AS REGION
    FROM table_1
    group by CASE WHEN GEOGRAPHY = 'CANADA' THEN 'NA'
    WHEN GEOGRAPHY = 'UNITED STATES' THEN 'NA'
    WHEN GEOGRAPHY = 'KOREA' THEN 'AP'
    WHEN GEOGRAPHY = 'CHINA' THEN 'AP'
    END, GEOGRAPHY, DATE

    --John

    "Matt Offutt" <matt_offutt@hp.com> wrote:
    >
    >In SQL Server 7 I'd like to use a 'column' that is created in a Select statement
    >(REGION)in the Group by, however I am getting an error indicating an invalid
    >column name.
    >
    >Any suggestions?
    >
    >Thanks for your help.
    >-------------------------------------------
    >
    >select GEOGRAPHY, DATE, Count(*) AS REC_COUNT,
    > CASE WHEN GEOGRAPHY = 'CANADA' THEN 'NA'
    > WHEN GEOGRAPHY = 'UNITED STATES' THEN 'NA'
    > WHEN GEOGRAPHY = 'KOREA' THEN 'AP'
    > WHEN GEOGRAPHY = 'CHINA' THEN 'AP'
    > END AS REGION
    >FROM table_1
    >group by REGION, GEOGRAPHY, DATE



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center