Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2002
    Location
    Montreal, Canada
    Posts
    44

    Unanswered: Union Select Queries

    I have a Union query in an Access database and i want to use it in my SQL 2000 Server database. How is it possible for me to do this? Any help would be greatly appreciated.

    Thanks

  2. #2
    Join Date
    Dec 2002
    Posts
    63
    Post the access code so we can take a look at what may be broken.

  3. #3
    Join Date
    Dec 2002
    Location
    Montreal, Canada
    Posts
    44
    This is the MSAcess UNION query that runs fine in MSAccess. Does MSSQL SERVER 2000 have UNION query functionality?


    SELECT * From qryPOvsBookingPOResult
    UNION SELECT * From qryPOvsBookingBookingResult
    ORDER BY StyleNo;


    By the way, qryPOvsBookingPOResult and qryPOvsBookingBookingResult have been created as View's in SQLServer 2000.

  4. #4
    Join Date
    Dec 2002
    Posts
    63
    As long as you have the same number of columns in both views, and the columns are in the same order, what you have there should work perfectly.

  5. #5
    Join Date
    Dec 2002
    Location
    Montreal, Canada
    Posts
    44
    i do have the same number of fields in each view, so why do i get this error when trying to create the UNION query as a view.

    The Query Designer does not support the UNION SQL construct.

  6. #6
    Join Date
    Dec 2002
    Posts
    63
    What are you using to run the union? Try opening up Query Analyzer and run the above code in there. you will see that it works.

  7. #7
    Join Date
    Dec 2002
    Location
    Montreal, Canada
    Posts
    44
    great but how do i save the SQL statement as a View or whatever other object so as i can use it (select it) from within Crystal Reports?

  8. #8
    Join Date
    Dec 2002
    Location
    Montreal, Canada
    Posts
    44
    anyone?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    is it a view you want? i don't use crystal, so i'm not sure what it requires

    run this in query analyzer --

    CREATE VIEW viewname AS query

    i think microsoft sql server will let you use a UNION query in that syntax

  10. #10
    Join Date
    Mar 2002
    Posts
    192
    Hi Sammy:

    I am trying to do the same thing you are right now. You cannot run action queries in the "View" section of SQL Server. I believe the same applies to UNION queries as well. My gut feeling right now is to do what I do with action queries which is to pop the UNION query into a stored procedure and then execute the stored procedure.

  11. #11
    Join Date
    Mar 2002
    Posts
    192
    OK figured it out.

    Place the UNION QUERY in a stored procedure. For example.

    CREATE PROCEDURE spImportServiceIncidentDetail

    AS

    SELECT * FROM [qryImportServicePartDetail-Contract] UNION
    SELECT * FROM [qryImportServicePartDetail-Normal] UNION
    SELECT * FROM [qryImportServiceLabourDetail-Contract] UNION
    SELECT * FROM [qryImportServiceLabourDetail-Normal] UNION SELECT * FROM [qryImportServiceRepairOutDetail]
    ORDER BY InvoiceDetailDynamicsID;

    GO

    Then in your vb code using ado set the recordset object = to the command. i.e.

    set rs = cm.execute

    You will of course need to make sure you set up all the ADO object properties and ADO variables properly but that is the line that does it.

  12. #12
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    Originally posted by Sammy_S
    This is the MSAcess UNION query that runs fine in MSAccess. Does MSSQL SERVER 2000 have UNION query functionality?


    SELECT * From qryPOvsBookingPOResult
    UNION SELECT * From qryPOvsBookingBookingResult
    ORDER BY StyleNo;


    By the way, qryPOvsBookingPOResult and qryPOvsBookingBookingResult have been created as View's in SQLServer 2000.
    I understand that you want to create a VIEW with this SQL syntax:

    SELECT * From qryPOvsBookingPOResult
    UNION SELECT * From qryPOvsBookingBookingResult
    ORDER BY StyleNo;


    ???


    Try this


    CREATE VIEW dbo.VIEW2
    AS
    SELECT TOP 100 PERCENT * FROM qryPOvsBookingPOResult
    UNION
    SELECT TOP 100 PERCENT * From qryPOvsBookingBookingResult
    FROM dbo.Banci
    ORDER BY CodBanca


    You need the TOP 100 PERCENT workaround if you want to use the clause ORDER BY in a view (with UNION, or without union clause). This is because MS SQL Server 2000 is an ANSI SQL'92 standard compliant (I don't know why it has to be so strict, but this is what Microsoft says about this behaviour)

  13. #13
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Use ADO as a data source.

  14. #14
    Join Date
    Mar 2002
    Posts
    192
    Hi IO Nut:

    Once the union query is written, how do you save the query as a view in SQL Server?




    Originally posted by ionut calin
    I understand that you want to create a VIEW with this SQL syntax:

    SELECT * From qryPOvsBookingPOResult
    UNION SELECT * From qryPOvsBookingBookingResult
    ORDER BY StyleNo;


    ???


    Try this


    CREATE VIEW dbo.VIEW2
    AS
    SELECT TOP 100 PERCENT * FROM qryPOvsBookingPOResult
    UNION
    SELECT TOP 100 PERCENT * From qryPOvsBookingBookingResult
    FROM dbo.Banci
    ORDER BY CodBanca


    You need the TOP 100 PERCENT workaround if you want to use the clause ORDER BY in a view (with UNION, or without union clause). This is because MS SQL Server 2000 is an ANSI SQL'92 standard compliant (I don't know why it has to be so strict, but this is what Microsoft says about this behaviour)

  15. #15
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    He posted it already.
    CREATE VIEW ...
    and it is saved. See BOL.

Posting Permissions

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