MS SQL 2000 UNION Limitation

[victorthomas]

I have a union query which has perfect syntax and is a legal statement.

SELECT ID, Name, Company, Fax1, Notes
FROM dbo.right_fax_unified_phonebook
WHERE (Fax1 IS NOT NULL)
UNION
SELECT ID, Name, Company, Fax2, Notes
FROM dbo.right_fax_unified_phonebook1
WHERE (Fax2 IS NOT NULL)

The query runs but the Query designer refuses to save the view. The error message that I get is:

"View definiation includes no output columns or includes no items in the FROM clause.

Apparently this is known limitation in MS SQL, does anyone know of a workaround to this?

I don't understand how SQL 2000 is "SQL Compliant" but won't save a statement containing the UNION command?

 

[pweegar]

What service pack are you at? You might also want to do a google search for work arounds or fixes.

 

[JohnDTampaBay]

Do you get the same error if you try to create the view in Query Analyzer? Enterprise Manager has a few bugs related to the Query Designer, causing errors even though the SQL is legitimate.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[vongrunt]

JohnDTampaBay is correct - EM query designer cannot resolve UNION queries.

 

[ESquared]

I have run across this, too. If you are just trying to save the darn thing, often you can get the job done by cutting out everything but the first SELECT statement and saving it.

Then, paste all the rest back in and save again. It seems it has more difficulty with the initial save/parse than subsequent ones.

You can also avoid the annoying error messages about not supporting UNION and CASE by clicking the buttons to hide the Diagram and Grid panes.

-------------------------------------
A sacrifice is harder when no one knows you've made it.