none
Views not returning complete data RRS feed

  • Question

  • I recently started a factory position that uses SQL to store machine data. One of the Views in a database is set-up to return values for about 10 different columns. The problem is that the Views query only returns a very small portion of results from the past year. Why am I only getting a few results out of many thousands?

    I apologize in advance for the lack of technical expertise. I'm literally the only person in my facility who is responsible for all of this, and I'm new. 

    Tuesday, December 10, 2019 4:03 PM

All replies

  • you may want to check the view definition to see if there are filters applied there that's limiting the data.

    Also, make sure your query does not have TOP (10) or some thing like thats limiting the returned result set

    to view definition, expand Views in object explorer and right click on the view name and select "script as".

    you need to have view definition permission to do this.


    Hope it Helps!!

    Tuesday, December 10, 2019 5:06 PM
  • Hi Robert A. Perry,

     I do an example to make you clear. The data presented by a view depends on its definition. I suggest you look at your definition first. Please check .

    IF OBJECT_ID('test') IS NOT NULL drop table  test   
    go 
    create table test (
    id int , value varchar(5)
    )
    insert into test values (1,'A'),(2,'B'),(3,'C'), (4,'D')
    select * from test
    /*
    id          value
    ----------- -----
    1           A
    2           B
    3           C
    4           D
    */ 
    go
    create view test_view as 
    select * from test where id >2
    go
    select * from test_view
    /*
    id          value
    ----------- -----
    3           C
    4           D
    */
    
    -----Please check view definition
    SELECT TABLE_NAME as ViewName,
    VIEW_DEFINITION as ViewDefinition
    FROM INFORMATION_SCHEMA.Views
    where TABLE_NAME='test_view'
    /*
    ViewName                           ViewDefinition
    ---------------------------------- ------------------------------------
    test_view                          create view test_view as 
    								   select * from test where id >2
    */

    Hope it will help you.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 11, 2019 5:56 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 20, 2019 7:23 AM
  • Hi,

     

    If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and also encourage the community member to keep working on your issues.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 25, 2019 5:41 AM