Results 1 to 10 of 10

Thread: Union Trouble

  1. #1
    Join Date
    Sep 2002
    Location
    Italy
    Posts
    21

    Red face Unanswered: Union Trouble

    In a SQL 2000 SP 1 installation I've a problem with a view.

    When I run a select from this view with a where clause the view return me a result set, but if I insert a order by clause the result set is empty.
    I try to re-organize indexes and update statistics without results.

    The server is on a client site so I can't update the SQL 2000 installation immediatly, I try to ask about the installation of the SP 3.

    But in the meanwhile I need some information. Anyone see this problem before.

    Thanks in advice for any help!

  2. #2
    Join Date
    Jan 2004
    Posts
    49
    RTFM....
    Syntax
    CREATE VIEW [< owner > . ] view_name [ ( column [ ,...n ] ) ]
    [ WITH < view_attribute > [ ,...n ] ]
    AS
    select_statement
    [ WITH CHECK OPTION ]

    < view_attribute > ::=
    { ENCRYPTION | SCHEMABINDING | VIEW_METADATA }


    select_statement

    Is the SELECT statement that defines the view. It can use more than one table and other views. To select from the objects referenced in the SELECT clause of a view created, it is necessary to have the appropriate permissions.

    A view does not have to be a simple subset of the rows and columns of one particular table. A view can be created using more than one table or other views with a SELECT clause of any complexity.

    In an indexed view definition, the SELECT statement must be a single table statement or a multitable JOIN with optional aggregation.

    There are a few restrictions on the SELECT clauses in a view definition. A CREATE VIEW statement cannot:

    Include COMPUTE or COMPUTE BY clauses.


    Include ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement.


    Include the INTO keyword.


    Reference a temporary table or a table variable.
    Because select_statement uses the SELECT statement, it is valid to use <join_hint> and <table_hint> hints as specified in the FROM clause. For more information, see FROM and SELECT.

    Functions can be used in the select_statement.

    select_statement can use multiple SELECT statements separated by UNION or UNION ALL.

  3. #3
    Join Date
    Sep 2002
    Location
    Italy
    Posts
    21

    more info

    Sorry, but I miss to include an example so the message is not so clear.

    I create a view with a union like this

    CREATE VIEW MyView AS
    SELECT ... From T1 join T2 ... WHERE ...
    UNION
    SELECT ... FROM T10 join t11 ... WHERE ...

    without any ORDER BY CLAUSE.

    Then I the following queries from the view.

    SELECT * FROM MyView WHERE ColX = 'XYZ'

    SELECT * FROM MyView WHERE ColX = 'XYZ'
    ORDER BY ColK

    The first query return a result set but the second doesn't

    Thanks anyway

  4. #4
    Join Date
    Jan 2004
    Posts
    49
    What was error message?

  5. #5
    Join Date
    Sep 2002
    Location
    Italy
    Posts
    21
    No error message.
    Simply no result set with order by. Sobh!

  6. #6
    Join Date
    Nov 2003
    Posts
    94
    I suggest you run the ordering query on the view in Query Analyser with Menu->Query->Show Execution Plan enabled, and study the rowcount numbers between the last stages (upper leftmost) of the query.

    You may be getting an unintended DISTINCT-ing effect from the order by clause. Usually views are generated and then filtered, but the query optimisier will often bundle the commands outside a view set with those inside a view set because its more efficient to do so.

    Also bare in mind UNION merges non-distinct record sets to be distinct, you may actually require UNION ALL to get the result you want.

    Check the actual plan and find where the row counts are being reduced to zero.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ya know...

    First, I find it had to believe...and order by doesn't have any filtering affect on the result set

    Second, why not post the DDL of the tables and the actual view. Sample data wouldn't hurt either....

    I'm sure we can hook you up.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Sep 2002
    Location
    Italy
    Posts
    21
    In attach you find DDL of views and tables involved in the queries.
    I'm sorry but they are very complex and not properly clear.
    In attach there are also the Execution Plan of the query with only the WHERE clause and the Execution Plan of the query with WHERE and ORDER.

    Some other info:
    - the second query in the union (v4_pp_fact) in this particular case contains no data (also without the WHERE clause)
    - I've run DBCC CHECKDB and there're no error

    Thanks a lot for your help.
    Attached Files Attached Files

  9. #9
    Join Date
    Nov 2003
    Posts
    94
    Originally posted by Brett Kaiser
    Ya know...

    First, I find it had to believe...and order by doesn't have any filtering affect on the result set

    TOP 5 PERCENT .... ORDER BY ?

  10. #10
    Join Date
    Nov 2003
    Posts
    94
    I don't have forever spare, but this is my first guess.

    There are a number of conditionals that resolve to:

    SEEK :
    [posizione_do].[d_operazione] = [posizione_do].[d_operazione] - 'Jan 2 1900 12:00AM'

    Which seems strange (where x = x -1?) unless refence is intended between one instance of a d_operazione and another.

    [posizione_do as A].[d_operazione] = [posizione_do as B].[d_operazione] - 'Jan 2 1900 12:00AM'

    This assumes B is greater in value than A - are you cross sorting a subset of the data out of the overall query?

Posting Permissions

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