Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Razor1 (IS/IT--Management)
1 Sep 09 10:58
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 (Programmer)
1 Sep 09 11:06
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 (Programmer)
1 Sep 09 11:09
the (nolock) has to go after each table that you want to use it on.

so

CODE

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
 

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close