Results 1 to 7 of 7

Thread: Union queries

  1. #1
    Join Date
    May 2004
    Posts
    9

    Unanswered: Union queries

    In v. 7 I could create union queries and save them as views, which I found very usefull for feeding data to reporting apps etc. In SQL 2000 the view builder balks at this. (The message states "View definition includes no output columns or includes no items in the from clause.") Version 7 used to complain as well but would still save the view (it's just a text SQL statement, after all). Is it still possible to save a union query as a view in SQL 2000? The view I was trying to create this morning is:

    SELECT idx, slots_idx, partno, priority
    FROM table 1
    UNION ALL
    SELECT idx, slots_idx, partno, priority
    FROM table 2

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311
    We use Union to create views in our environment all the time. Can you post your queries?

  3. #3
    Join Date
    May 2004
    Posts
    9
    Joe,

    The query string was included at the end of the original post. I used to be able to save union views but after migration from SQL 7 to SQL 2000 I am no longer able to save a view containing a union clause. I have to admit that I am baffled by this. Are you using SQL 2000? Is there some environment variable that I have set incorrectly??

  4. #4
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311
    We run everything on SQL 2000. Are you doing indexed views? Indexed views will not allow you to use Union. Do you have schemabinding in your create view statement? If you are creating a view with schemabinding clause, you will get that error message too.

  5. #5
    Join Date
    May 2004
    Posts
    9
    No indexes on the view (I think you need enterprise version for that anyway, running standard here) and no shcemabinding involved either...

  6. #6
    Join Date
    Jul 2004
    Posts
    52
    Is there really a space between table and 1? If so you are aliasing table as 1 and numbers are not valid SQL identifiers. Try t1 instead of 1.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It's either [table 1] or [1] that needs to be used.

    The only error you may get from the GUI of a view wisard would be this:

    Error: The Query Designer does not support the UNION SQL construct.

    Unless your naming is off like I suggestde earlier.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •