SQL View not running results but running query that makes view does return results

Question

I have a view has a total of about 10 left joins in it.

If I execute the view directly, I get results but they aren't complete. If I execute the query that makes up the view directly, I get a full set of results.

I ran sp_refreshView and the issue is still present.

I've never encountered an issue like this and I'm not sure exactly what to do to resolve it. Does anybody have any potential solutions or troubleshooting steps I can take to try to resolve the issue?

I can confirm that both queries are being run against the same database. The db is on my local instance of SQL Server and it is the only db I can run against.

I am calling the view with the following query and getting no results:

SELECT *  
FROM [DHTraxxDev].[Inventory].[vwInventoryList]  
WHERE ProjectID = 46

This works with some project numbers and not with others.

If I add an and clause to the end of the create view where ps.ProjectID = 46, then I get the relevant information.

The code to create the view is

CREATE VIEW [Inventory].[vwInventoryList]
AS
    SELECT 
        ps.ID AS ProjectSpecID, 
        psrd.id AS 'ProjectSpecReceiveDetailId', 
        ps.ModelNumber, 
        p.Identifier, p.ProjectName, p.SiteName, 
        ps.ProjectID, ps.SpecNumber, ps.ProjectSpecImportFileID, 
        ps.GenericDescription, ps.DetailedDescription, 
        ps.LeadTime, ps.Quarter, ps.QuarterYear, 
        ps.ESACategory, ps.Scope AS ESAScope, 
        ps.GLCode AS ESAGLCode, ps.ShipToDestinationId, 
        ps.StatusID AS ProjectSpecStatus, 
        ISNULL(ps.AtticQuantity, 0) AS AtticQuantity, 
        ISNULL(ps.Quantity, 0) AS Quantity, 
        ISNULL(ps.AtticQuantity, 0) + ISNULL(ps.Quantity, 0) AS TotalQuantity, 
        ISNULL(ps.UnitCost, 0) AS UnitCost, 
        v.Name AS Vendor, luc.Description AS UnitType, 
        ps.EstimatedDeliveryDate, ps.EstimatedShippingDate, 
        ps.ProcurementNotes, 
        lpsl.Description AS 'ShipTo',
        psc.Description AS 'ComponentDescription', 
        po.ID AS PoId, po.StatusID AS POStatus, 
        po.Identifier AS PurchaseOrderIdentifier, 
        psc.ID AS ProjectSpecComponentID, 
        psc.SpecNumber AS ComponentSpecNumber, 
        psc.Multiplier,
        (ISNULL(ps.AtticQuantity, 0) + ISNULL(ps.Quantity, 0)) + COALESCE(psrToVendor.ToVendor, 0) - COALESCE(psrFromVendor.FromVendor, 0) AS 'QuantityAtVendor',
        COALESCE(psrToWarehouse.ToWarehouse, 0) - COALESCE(psrFromWarehouse.FromWarehouse, 0) AS QuantityAtWarehouse,
        COALESCE(psrToSite.ToSite, 0) - COALESCE(psrFromSite.FromSite, 0) AS QuantityAtSite,
        COALESCE(psrComponentToWarehouse.ToWarehouse, 0) - COALESCE(psrComponentFromWarehouse.FromWarehouse, 0) AS ComponentQuantityAtWarehouse,
        COALESCE(psrComponentToSite.ToSite, 0) - COALESCE(psrComponentFromSite.FromSite, 0) AS ComponentQuantityAtSite,
        (ISNULL(ps.AtticQuantity, 0) + ISNULL(ps.Quantity, 0)) * ISNULL(ps.UnitCost, 0) AS TotalCost, 
        (COALESCE(psrToWarehouse.ToWarehouse, 0) - COALESCE(psrFromWarehouse.FromWarehouse, 0)) * ISNULL(ps.UnitCost, 0) AS WarehouseCost, 
        (COALESCE(psrToSite.ToSite, 0) - COALESCE(psrFromSite.FromSite, 0)) * ISNULL(ps.UnitCost, 0) AS SiteCost,
        COALESCE(pendingInventory.Pending, 0) AS Pending,
        COALESCE(allocatedInventory.Allocated, 0) AS Allocated
    FROM            
        Project.ProjectSpec AS ps 
    INNER JOIN
        Project.Project AS p ON p.ID = ps.ProjectID 
    LEFT OUTER JOIN
        Project.lkupProjectSpecLocation AS lpsl ON lpsl.ID = ps.ShipToDestinationId 
    LEFT OUTER JOIN
        DH.Vendor AS v ON ps.VendorID = v.ID 
    LEFT OUTER JOIN
        PurchaseOrder.lkupUnitCost AS luc ON luc.ID = ps.UnitCostID 
    LEFT OUTER JOIN
        Project.ProjectSpecComponent AS psc ON psc.ProjectSpecID = ps.ID 
    LEFT OUTER JOIN
        PurchaseOrder.PurchaseOrder AS po ON po.ID = ps.PurchaseOrderID 
    LEFT OUTER JOIN
        Project.ProjectSpecReceivedDetails psrd on psrd.ProjectSpecID = ps.ID 
    LEFT JOIN 
        (SELECT  
             ProjectSpecID, SUM(ri.amount) AS 'Pending'
         FROM
             Inventory.Request r 
         INNER JOIN
             inventory.requestinventory ri ON r.id = ri.RequestID
         WHERE 
             RequestTypeID = 1 AND r.Deleted = 0 AND ri.Deleted = 0 
             AND RequestStatusID = 3
         GROUP BY
             ProjectSpecID) pendingInventory ON pendingInventory.ProjectSpecID = ps.ID
     LEFT JOIN 
         (SELECT
              ProjectSpecID, SUM(ri.amount) AS 'Allocated'
          FROM 
              Inventory.Request r 
          INNER JOIN
              inventory.requestinventory ri ON r.id = ri.RequestID
          WHERE
              RequestTypeID = 2 AND r.Deleted = 0 
              AND ri.Deleted = 0 AND RequestStatusID = 4
          GROUP BY
              ProjectSpecID) allocatedInventory ON allocatedInventory.ProjectSpecID = ps.ID
    LEFT JOIN
        (SELECT
             psrd.ProjectSpecID, psrd.ProjectSpecComponentID,
             ToSite = SUM(psrd.QuantityReceived)
         FROM 
             Project.ProjectSpecReceivedDetails psrd
         WHERE
             psrd.Deleted = 0
             AND psrd.ToProjectSpecLocationID = 3
             AND psrd.ProjectSpecComponentID IS NOT NULL
         GROUP BY 
             psrd.ProjectSpecID, psrd.ProjectSpecComponentID) psrComponentToSite ON psrComponentToSite.ProjectSpecID = ps.ID
    LEFT JOIN
        (SELECT
             psrd.ProjectSpecID, psrd.ProjectSpecComponentID
        ,FromSite = SUM(psrd.QuantityReceived)
    FROM Project.ProjectSpecReceivedDetails psrd
    WHERE
        psrd.Deleted = 0
        AND psrd.FromProjectSpecLocationID = 3
        and psrd.ProjectSpecComponentID is not null
    GROUP BY psrd.ProjectSpecID, psrd.ProjectSpecComponentID
) psrComponentFromSite ON psrComponentFromSite.ProjectSpecID = ps.ID
LEFT JOIN
(
    SELECT
        psrd.ProjectSpecID, psrd.ProjectSpecComponentID
        ,ToWarehouse = SUM(psrd.QuantityReceived)
    FROM Project.ProjectSpecReceivedDetails psrd
    WHERE
        psrd.Deleted = 0
        AND psrd.ToProjectSpecLocationID = 2
        and psrd.ProjectSpecComponentID is not null
    GROUP BY psrd.ProjectSpecID, psrd.ProjectSpecComponentID
) psrComponentToWarehouse ON psrComponentToWarehouse.ProjectSpecID = ps.ID
LEFT JOIN
(
    SELECT
        psrd.ProjectSpecID, psrd.ProjectSpecComponentID
        ,FromWarehouse = SUM(psrd.QuantityReceived)
    FROM Project.ProjectSpecReceivedDetails psrd
    WHERE
        psrd.Deleted = 0
        AND psrd.FromProjectSpecLocationID = 2
        and psrd.ProjectSpecComponentID is not null
    GROUP BY psrd.ProjectSpecID, psrd.ProjectSpecComponentID
) psrComponentFromWarehouse ON psrComponentFromWarehouse.ProjectSpecID = ps.ID
LEFT JOIN
(
    SELECT
        psrd.ProjectSpecID
        ,ToSite = SUM(psrd.QuantityReceived)
    FROM Project.ProjectSpecReceivedDetails psrd
    WHERE
        psrd.Deleted = 0
        AND psrd.ToProjectSpecLocationID = 3
        and psrd.ProjectSpecComponentID is null
    GROUP BY psrd.ProjectSpecID
) psrToSite ON psrToSite.ProjectSpecID = ps.ID
LEFT JOIN
(
    SELECT
        psrd.ProjectSpecID
        ,FromSite = SUM(psrd.QuantityReceived)
    FROM Project.ProjectSpecReceivedDetails psrd
    WHERE
        psrd.Deleted = 0
        AND psrd.FromProjectSpecLocationID = 3
        and psrd.ProjectSpecComponentID is null
    GROUP BY psrd.ProjectSpecID
) psrFromSite ON psrFromSite.ProjectSpecID = ps.ID
LEFT JOIN
(
    SELECT
        psrd.ProjectSpecID
        ,ToWarehouse = SUM(psrd.QuantityReceived)
    FROM Project.ProjectSpecReceivedDetails psrd
    WHERE
        psrd.Deleted = 0
        AND psrd.ToProjectSpecLocationID = 2
        and psrd.ProjectSpecComponentID is null
    GROUP BY psrd.ProjectSpecID
) psrToWarehouse ON psrToWarehouse.ProjectSpecID = ps.ID
LEFT JOIN
(
    SELECT
        psrd.ProjectSpecID
        ,FromWarehouse = SUM(psrd.QuantityReceived)
    FROM Project.ProjectSpecReceivedDetails psrd
    WHERE
        psrd.Deleted = 0
        AND psrd.FromProjectSpecLocationID = 2
        and psrd.ProjectSpecComponentID is null
    GROUP BY psrd.ProjectSpecID
) psrFromWarehouse ON psrFromWarehouse.ProjectSpecID = ps.ID
LEFT JOIN
(
    SELECT
        psrd.ProjectSpecID
        ,ToVendor = SUM(psrd.QuantityReceived)
    FROM Project.ProjectSpecReceivedDetails psrd
    WHERE
        psrd.Deleted = 0
        AND psrd.ToProjectSpecLocationID = 1
        and psrd.ProjectSpecComponentID is null
    GROUP BY psrd.ProjectSpecID
) psrToVendor ON psrToVendor.ProjectSpecID = ps.ID
LEFT JOIN
(
    SELECT
        psrd.ProjectSpecID
        ,FromVendor = SUM(psrd.QuantityReceived)
    FROM Project.ProjectSpecReceivedDetails psrd
    WHERE
        psrd.Deleted = 0
        AND psrd.FromProjectSpecLocationID = 1
        and psrd.ProjectSpecComponentID is null
    GROUP BY psrd.ProjectSpecID
) psrFromVendor ON psrFromVendor.ProjectSpecID = ps.ID
WHERE        (ps.Deleted = 0) AND (psc.ID IS NULL) OR
                         (ps.Deleted = 0) AND (psc.Deleted = 0)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "ps"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 136
               Right = 295
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "p"
            Begin Extent = 
               Top = 6
               Left = 333
               Bottom = 136
               Right = 572
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "lpsl"
            Begin Extent = 
               Top = 6
               Left = 610
               Bottom = 102
               Right = 780
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "v"
            Begin Extent = 
               Top = 6
               Left = 818
               Bottom = 136
               Right = 1014
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "luc"
            Begin Extent = 
               Top = 102
               Left = 610
               Bottom = 215
               Right = 780
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "psc"
            Begin Extent = 
               Top = 138
               Left = 38
               Bottom = 268
               Right = 295
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "po"
            Begin Extent = 
               Top = 138
               Left = 333
               Bottom = 268
               Right = 549
            End
            DisplayFlags = 280
            TopColumn = 0
       ' , @level0type=N'SCHEMA',@level0name=N'Inventory', @level1type=N'VIEW',@level1name=N'vwInventoryList'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'  End
         Begin Table = "rdi"
            Begin Extent = 
               Top = 138
               Left = 818
               Bottom = 268
               Right = 1044
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'Inventory', @level1type=N'VIEW',@level1name=N'vwInventoryList'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'Inventory', @level1type=N'VIEW',@level1name=N'vwInventoryList'
GO

Please provide code for view generation and how you are calling it — Brad, Nov 29 '18 at 17:45

score 0 · Accepted Answer · answered Nov 29 '18 at 18:06

0

I would try rewriting that WHERE clause:

WHERE (ps.Deleted = 0) AND COALESCE(psc.Deleted, 0) = 0

answered Nov 29 '18 at 18:06

Joe Stefanelli

132,803
19
237
235

Thanks for the suggestion! My intent with the original where clause was to make sure that all ps was included if they were not deleted and to include their dependent items (psc) if they are there and not deleted. Is the way you wrote the query more efficient in execution or is it just cleaner (and it is way way cleaner, thank you again)? – JakeHova Nov 29 '18 at 18:10
@JakeHova Did this resolve the issue? My concern was referencing the left-joined column in `...AND (psc.Deleted = 0)` would force the join to behave like an inner join and therefore give incorrect results. – Joe Stefanelli Nov 29 '18 at 18:13
I'm not sure if it resolved the issue directly because I dropped the SP and re-created it and it started returning results again. But your update did resolve another issue that I didn't know I had where results were being limited on return because of what you said. I do have a question about that though, when I was fisrt creating the view, I had parantheses around the two clauses on either side of the OR statement. When I went back to look at the resultant view script, the grouping parantheses were gone. Does SQL ignore paranthetical grouping? – JakeHova Nov 29 '18 at 18:21
@JakeHova I think this question/answers explains what you're asking: https://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated – Joe Stefanelli Nov 29 '18 at 18:33

SQL View not running results but running query that makes view does return results

1 Answers1