I am trying to use a derived Table and this will not work for me. Obviously,
I am NOT going to SELECT *, but for demonstration purposes, here it is.

SELECT    *
FROM    (SELECT    Filename,
        RACE    =
            CASE    WHEN    BRaceAA    =    'X'    THEN    '1,'    ELSE    ''    END    +
            CASE    WHEN    BRaceA    =    'X'    THEN    '2,'    ELSE    ''    END    +
            CASE    WHEN    BRaceB    =    'X'    THEN    '3,'    ELSE    ''    END    +
            CASE    WHEN    BRaceNH    =    'X'    THEN    '4,'    ELSE    ''     END    +
            CASE    WHEN    BRaceW    =    'X'    THEN    '5,'    ELSE    ''    END   
    FROM    HMDAFromPointIMP
    WHERE    BRaceAA    IS    NOT NULL
    OR    BRaceA    IS    NOT NULL
    OR    BRaceB    IS    NOT NULL
    OR    BRaceNH    IS    NOT NULL
    OR    BRaceW    IS    NOT NULL)

It is giving me Incorrect Syntax on the last ")"

You need to give the derived table an alias, like I demonstrated in an
earlier thread about this same issue:

FROM
(
    SELECT /* blah blah */
) x
--^ alias

Please stop starting new threads!





Show quoteHide quote

    As a member of the B,NH,W race, I take exception to your suggestion that
new threads not be created!  We are a proud race of thread-starters.

....and I thought Tiger Woods was supposed to be white, Asian and black. This
is the fourth socially disturbing thread today. Plus there's approximately 30
different races in the world.

This could make such a nice relational model...


ML

In addition to the syntax error that my other fellow posters have mentioned,
you might want to consider (if you can) changing your table structures.
Having columns like this is messy (as your query demonstrates)

You might want to do something like:

HMDAFromPointIMP
===============
HMDAFromPointIMP_key --pk
<other columns>

HMDAFromPointIMPRace
===================
HMDAFromPointIMP_key --pk
Race_key                            --pk

Race
====
Race_key --pk
RaceCode --AA, B,NH, etc
Description --

Then adding a new race is easier, and building queries is also much easier.
Just an idea if you are building a new system :)


Show quoteHide quote

I smell a big, fat compiler bug
How do you rate this script
SET ROWCOUNT!
Number of transactions
ATTENTION T-SQL GODS :: ALTER FUNCTION Permissions Issue