I am trying to extract all of the supervisors that work in an office.  They
can appear in two different columns.  Some appear in one column and not the
other, and some in both.  I have the following code:

SELECT DISTINCT [Supervisor]
FROM (
    SELECT DISTINCT [InSupervisor] AS [Supervisor]
    FROM [TOFC].[dbo].[temp_hold]
    UNION
    SELECT DISTINCT [OutSupervisor] AS [Supervisor]
    FROM [TOFC].[dbo].[temp_hold]
)

However, when I try to execute it, I get an error about incorrect syntex
near the closing parenthesis.  How can I get this to work?

Thanks in advance

You have no alias for the derived table in your example:

SELECT ..
  FROM ( SELECT ...
          UNION
         SELECT ...
       ) AS "Derived"      --- this is missing

As Anith mentioned, you need an alias for your derived table.

As a side note, you could probably simplify the statement a little.

UNION will remove the duplicates already so the top-most SELECT DISTINCT is
unnecessary (you would use UNION ALL if you wanted to retain the
duplicates).

Also, in rows where the supervisor's name only appears in one column, what
appears in the other column? If it is Null, you could make the statement
look like this:

SELECT DISTINCT
     COALESCE(InSupervisor, OutSupervisor) AS Supervisor
FROM
     [TOFC].[dbo].[temp_hold]

This will return InSupervisor if it is not null, and will return
OutSupervisor otherwise. So if they are both filled in, you will get
InSupervisor.

If the unused column is not null when not used, you can still use a similar
technique. Just test for the "unused" condition in a case expression. For
example, if the unused column contains 'Unknown', then you could use:

SELECT DISTINCT
     CASE WHEN InSupervisor = 'Unknown' THEN OutSupervisor ELSE InSupervisor
END AS Supervisor
FROM
     [TOFC].[dbo].[temp_hold]

Since you did not provide DDL or sample data, it is difficult to know
exactly what your information looks like, but I am guessing there is some
variation of what I have shown above that would help simplify you statement.
IHTH.

Show quoteHide quote

hi Chris,
Use the following code:

SELECT DISTINCT (
SELECT DISTINCT [InSupervisor] AS [Supervisor]
FROM [TOFC].[dbo].[temp_hold]
UNION
SELECT DISTINCT [OutSupervisor] AS [Supervisor]
FROM [TOFC].[dbo].[temp_hold])  AS [Supervisor]
FROM [TOFC].[dbo].[temp_hold]

Thanks,

Tarek Ghazali


Show quoteHide quote

Confusing Results
Audit trigger with dynamic SQL and Cursor - Am I close?
Novice Query question
Stored Procedure, Temp table or Cursor
book on performance tuning
HOST_NAME function peculiarity
Problem with update query based on subquery
Backing up tr.log on another server
SELECT DATETIME PROBLEM???
Very strange behaviour