>>>You should alias the derived table and also you should have at least one field in field list part of the SELECT:
>>>
>>>SELECT DISTINCT ??????
>>> FROM (select Well_5.DailyRuntime, Well_5.DailyStarts, Well_5.DailyFlowrate from Well_5
>>> union all
>>> select Well_6.DailyRuntime, Well_6.DailyStarts, Well_6.DailyFlowrate from Well_6) Tbl1
>>>
>>>BTW SELECT Distinct is redundant in that case. If you use just use UNION not UNION ALL that will give you DISTINCT records
>>>
>>>
>>>
>>>select Well_5.DailyRuntime, Well_5.DailyStarts, Well_5.DailyFlowrate from Well_5
>>>union
>>>select Well_6.DailyRuntime, Well_6.DailyStarts, Well_6.DailyFlowrate from Well_6
>>>
>>
>>I think I had some ideas why I wanted to use DISTINCT outside - don't remember right now. I wanted Jim to find this problem himself, but you gave it up :)
>
>Ok, please be patient with me. The query returns three columns. What I am looking for is six columns with each row being a single day. The query also returns extraneous rows having null values in at least one column.
Are you looking to PIVOT your data? What SQL version Server you're using?
Check sample here
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c83e30c2-056f-40a5-b6a5-5141be5bd4dd
If it's not broken, fix it until it is.
My Blog