none
VIEW results differ to underlying SELECT results

    Question

  • I have created the following simple view:

    CREATE VIEW cs.MyTestPO AS
    SELECT ttdpur401100.t_orno
    , ttdpur401100.t_pono
    , ttdpur401100.t_sqnb
    , ttdpur401100.t_item
    , ttcibd001100.t_dsca
    FROM ttdpur401100 
    	JOIN ttcibd001100 ON ttcibd001100.t_item = ttdpur401100.t_item

    Then SELECT * FROM cs.MyTestPO returns 276376 rows

    If I run the select statement by itself 

    SELECT ttdpur401100.t_orno
    , ttdpur401100.t_pono
    , ttdpur401100.t_sqnb
    , ttdpur401100.t_item
    , ttcibd001100.t_dsca
    FROM ttdpur401100 
    	JOIN ttcibd001100 ON ttcibd001100.t_item = ttdpur401100.t_item

    it returns 277488 rows

    Can anyone suggest why the view is missing 1112 rows that the SELECT statement returns.



    Monday, February 11, 2013 10:29 PM

Answers

  • Does it return two rows?

     

    select *
    from sys.tables
    where name in ('ttdpur401100', 'ttcibd001100')
     

    If there are more then two rows then there are same table names in different schemas with different content.



    • Edited by Fedor Pustovachenko Monday, February 11, 2013 11:59 PM
    • Marked as answer by Compac Tuesday, February 12, 2013 7:54 PM
    Monday, February 11, 2013 11:59 PM

All replies

  • Rebuild your indices and statistics on your table and - if they exists - on your view.
    Monday, February 11, 2013 10:37 PM
  • Does it return two rows?

     

    select *
    from sys.tables
    where name in ('ttdpur401100', 'ttcibd001100')
     

    If there are more then two rows then there are same table names in different schemas with different content.



    • Edited by Fedor Pustovachenko Monday, February 11, 2013 11:59 PM
    • Marked as answer by Compac Tuesday, February 12, 2013 7:54 PM
    Monday, February 11, 2013 11:59 PM
  • I find it odd that you schema qualify the view name, but not the underlying tables (or are they views?).  Perhaps you should qualify those names as well (which is a best practice in any event).
    Tuesday, February 12, 2013 2:31 PM
  • Excellent - many thanks for that.
    Tuesday, February 12, 2013 7:57 PM