With (NoLocks) not working

[Razor1]

I have 2 Views the one works using NOLocks the other on gives me an error. Any idea why?

This one works.

Alter view GT_V_PostedBatchesCYPY as

SELECT DISTINCT TOP 100 PERCENT ARFiscalYear, ARFiscalPeriod, ARBatchName
FROM dbo.ARPostingJournalHeader
With (nolock)
WHERE (ARFiscalYear = YEAR(GETDATE())) OR
(ARFiscalYear = YEAR(DATEADD(yyyy, - 1, GETDATE())))
ORDER BY ARBatchName



This one does not work

Alter view gt_V_SalesCY_West as

SELECT TOP 100 PERCENT GT_V_PostedBatchesCyPY.ARFiscalYear, GT_V_PostedBatchesCyPY.ARFiscalPeriod, dbo.Customers.CustomerNo, dbo.Customers.CustomerID,
dbo.Customers.Name AS CustName, dbo.Invoice.InvoiceNo, dbo.Invoice.CredInvoiceNo, dbo.InvoiceDetails.[lineno],
CASE WHEN InvoiceDetails.ItemNo = 'Lift Chairs'
THEN ItemSpecs.PropText ELSE InvoiceDetails.ItemNo END AS ItemNo,
dbo.InvoiceDetails.QtyOrdered, dbo.InvoiceDetails.QtyCredited, dbo.InvoiceDetails.ExtendedPrice, dbo.SalesReps.SalesRepCode,
dbo.SalesReps.SalesRepName, dbo.Categories.CategoryCode, dbo.ItemClasses.ClassCode, dbo.ItemClasses.DescriptionShort AS ItemClassName,
dbo.Categories.DescriptionShort AS CategoryName, dbo.Categories.CommissionPaid, dbo.Customers.BuyingGroupID,
dbo.BuyingGroups.DescriptionTiny AS BuyingGroup,dbo.Invoice.CreditReasonId,
Case when Left(dbo.Categories.CategoryCode,2) ='BD' then 'Beds'
when Left(dbo.Categories.CategoryCode,2) ='LC' then 'Lift Chairs'
when Left(dbo.Categories.CategoryCode,5) ='SCBUZ' then 'BuzzAround'
when Left(dbo.Categories.CategoryCode,5) ='SCVCM' then 'VA Scooters'
when Left(dbo.Categories.CategoryCode,2) ='SC' and dbo.Categories.CategoryCode <> 'SCBUZ'
and dbo.Categories.CategoryCode <> 'SCVCM'
then Left(dbo.InvoiceDetails.ItemNo, 5)
when Left(dbo.Categories.CategoryCode,2) ='PB' then 'Powerbases'
when Left(dbo.Categories.CategoryCode,2) ='UC' then 'UltraComfort'
end as [Product],

Case when Left(dbo.Categories.CategoryCode,2) ='BD' then 'Beds'
when Left(dbo.Categories.CategoryCode,2) ='LC' then 'Lift Chairs'
when Left(dbo.Categories.CategoryCode,5) ='SCBUZ' then 'Scooters'
when Left(dbo.Categories.CategoryCode,5) ='SCVCM' then 'VA Scooters'
when Left(dbo.Categories.CategoryCode,2) ='SC' and dbo.Categories.CategoryCode <> 'SCBUZ'
and dbo.Categories.CategoryCode <> 'SCVCM'
then 'Scooters'
when Left(dbo.Categories.CategoryCode,2) ='PB'
then Left(dbo.InvoiceDetails.ItemNo, 5)
when Left(dbo.Categories.CategoryCode,2) ='UC' then 'UltraComfort'
end as [ProductGroup]


FROM dbo.Invoice INNER JOIN
dbo.InvoiceDetails ON dbo.Invoice.InvoiceID = dbo.InvoiceDetails.InvoiceID INNER JOIN
dbo.ItemSpecs ON dbo.InvoiceDetails.ItemSpecID = dbo.ItemSpecs.ItemSpecID INNER JOIN
dbo.GT_V_PostedBatchesCyPY ON dbo.Invoice.ARBatchFileName = GT_V_PostedBatchesCyPY.ARBatchName INNER JOIN
dbo.Items ON dbo.ItemSpecs.ItemID = dbo.Items.ItemID INNER JOIN
dbo.Customers ON dbo.Invoice.CustomerID = dbo.Customers.CustomerID INNER JOIN
dbo.Categories ON dbo.Items.CategoryID = dbo.Categories.CategoryID INNER JOIN
dbo.ItemClasses ON dbo.Items.ItemClassID = dbo.ItemClasses.ItemClassID INNER JOIN
dbo.SalesReps ON dbo.Invoice.SalesRepID = dbo.SalesReps.SalesRepID LEFT JOIN
dbo.BuyingGroups ON dbo.Customers.BuyingGroupID = dbo.BuyingGroups.BuyingGroupID
With(nolock)

WHERE (dbo.Categories.CommissionPaid = 1) AND GT_V_PostedBatchesCyPY.ARFiscalYear = year(getdate())
and substring(dbo.SalesReps.SalesRepCode,2,1) ='W'

Thanks in advance for any assistance.



Razor1

 

[RiverGuy]

In your first view, you have NOLOCK on your single table you are selecting from. In your second view, you have multiple tables, with NOLOCK on only one of the tables. However, in most cases, it is usually a bad idea to use NOLOCK. If I were you, I would work with your dba to optimize your query so as not to be forced to use dirty reads.

 

[SQLSister]

the (nolock) has to go after each table that you want to use it on.

so

FROM         dbo.Invoice (nolock)INNER JOIN
 dbo.InvoiceDetails (nolock) ON dbo.Invoice.InvoiceID = dbo.InvoiceDetails.InvoiceID INNER JOIN

etc.

"NOTHING is more important in a database than integrity." ESquared