SQL view doesn't return all records.

I removed it and use straight select, still the same.

Create VIEW [dbo].[LogoMasterInventoryReport]
AS
SELECT         LS.CompanyID,  LS.InventoryID,  LS.SubItemID, LS.SiteID, LS.LocationID,
                     LS.QtyOnHand,   LS.QtyOnHand AS [TotalQtyonHand]
      
FROM        dbo.INLocationStatus AS LS

We initially have group clause in the view and link multiple tables in the GI. We didn’t get the expected result. So I simplified view to just select fields without any group, and only include this view in GI without other tables. And I found the view doesn’t return all records.

Please see the screenshot below. The left screen is GI from the SQL view LogoMasterInventoryReport, that’s the only table in the GI. It has total 212213 records. The last record InventoryID stops at 19064.

The right screen is the GI from INLocationstatus inner join to InventoryItem. It has 212626 records. There are more records after InventoryID 19064. 

I don’t know why the SQL view stops at the InventoryID 19064.

@junmao01  This is going to sound counter-intuitive, but IMO, the issue is not your SQL view, but the GI with the INNER JOIN.
I think you are seeing too many rows returned in the INNER JOIN GI with the Inventory. (IE. you are returning duplicates in SQL in that GI).
What you might try is on your INNER JOIN GI, set grouping to LS.InventoryID and see then if the two GI’s return the same number of rows.  Or possibly using a LEFT JOIN instead.
What happens when you run those SQL commands natively in SSMS?
Below is an example where I took your SQL view statement and ran it against my DB natively, and then also ran it with an INNER JOIN on Inventory items.  You can see where the two SQL commands return different numbers of data sets.

IF you want to JOIN your INLocationStatus to your InventoryItem table as a SQL view, I have found it much better to use the following type of SQL construct. You can see the results in the third screenshot below.
    SELECT        TOP (100) PERCENT LS.CompanyID,  LS.InventoryID,  LS.SubItemID, LS.SiteID, LS.LocationID,
                                    LS.QtyOnHand,   LS.QtyOnHand AS [TotalQtyonHand],
                                    INV.[InventoryID],INV.[InventoryCD]
    FROM        dbo.INLocationStatus AS LS
    OUTER APPLY (
          SELECT TOP(1) *
          FROM [dbo].[InventoryItem]
          WHERE [dbo].[InventoryItem].[InventoryID] = LS.[InventoryID]
    ) INV
    WHERE LS.[CompanyID] = 2

 

I find the problem. From 2023 R2 the INSiteStatus and INLocationStatus are projection DAC now. The old records are still exist in the table. But new records will be added to actual table INSiteStatusByCostCenter and INLocationStatusByCostCenter. 

Thank you for sharing your solution with the community @junmao01!