Derived Table

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!
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:34AAF6C5-E9FF-4D7C-A26C-09DDF7223265@.microsoft.com...
>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 ")"|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OVFSQP2nFHA.420@.TK2MSFTNGP09.phx.gbl...
> Please stop starting new threads!
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.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||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 :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:34AAF6C5-E9FF-4D7C-A26C-09DDF7223265@.microsoft.com...
>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 ")"|||...and I thought Tiger Woods was supposed to be white, Asian and black. Thi
s
is the fourth socially disturbing thread today. Plus there's approximately 3
0
different races in the world.
This could make such a nice relational model...
ML