Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2

    Unanswered: Nightmare legacy query to unravel

    Afternoon all!

    This is going to get long. A user has flagged a potential problem with an SQL-based application - she's entered a record, and cannot see it where she thinks she should. This application is about 7 years old, built on SQL2000 and Access 2000. Furthermore, it was built in-house by two people who knew little about SQL and Access, and are no longer with the company.

    The application is called CCQuID (Credit Control Query Information Database), and is used by Credit Control to record the reasons given for why our customers aren't paying us. The problem that has been flagged is that a query has been entered and is not appearing on the front form. I have checked the design of the form, and the source view's SQL statement is as follows:
    Code:
    CREATE VIEW dbo.[vssqueryproductline(current)]
    AS
    SELECT TOP 100 PERCENT
    	dbo.SLP15_IN.[Date] AS InvoiceDate
    ,	dbo.QueryProductLines.QueryTypeID
    ,	dbo.QueryProductLines.PassedToID
    ,	dbo.SLP05.CNAM05
    ,	dbo.SLP05.Account
    ,	dbo.QueryProductLines.ProductCode
    ,	dbo.UOM.UOM
    ,	dbo.QueryProductLines.Quantity
    ,	dbo.QueryProductLines.PriceInvoiced
    ,	dbo.QueryProductLines.PriceClaimed
    ,	dbo.QueryProductLines.InvoiceReference
    ,	dbo.QueryProductLines.DebitReference
    ,	dbo.QueryProductLines.Note
    ,	dbo.QueryProductLines.DatePassed
    ,	dbo.QueryProductLines.ThisPartCleared
    ,	dbo.Ledgers.ControllerName
    ,	dbo.SLP15_IN.SOPN
    ,	dbo.SLP15_IN.PurchaseOrder
    ,	dbo.SLP15_IN.DespatchSite
    ,	dbo.QueryProductLines.DateDetailsEntered
    ,	dbo.QueryTypes.QueryTypeStatus
    ,	dbo.PassedTo.PassedTo
    ,	dbo.QueryProductLines.UOMID
    ,	dbo.QueryProductLines.QPLID
    ,	dbo.SLP15_IN.OutstandingAmount AS INOS
    ,	dbo.SLP15_DN.OutstandingAmount AS DNOS
    ,	dbo.SLP15_DN.Account AS DNAccount
    ,	dbo.SLP15_DN.SOPN AS DNSOPN
    ,	Ledgers_1.ControllerName AS DNControllerName
    ,	dbo.SLP15_IN.DSEQ
    FROM
    	dbo.SLP05
    FULL OUTER JOIN
    	dbo.SLP15_IN
    ON
    	dbo.SLP05.Account = dbo.SLP15_IN.Account
    FULL OUTER JOIN
    	dbo.Ledgers
    ON
    	dbo.SLP05.CRSP05 = dbo.Ledgers.CRSP05
    AND
    	dbo.SLP05.CRSP05 = dbo.Ledgers.CRSP05
    AND 
    	dbo.SLP05.CRSP05 = dbo.Ledgers.CRSP05
    FULL OUTER JOIN
    	dbo.SLP15_DN
    FULL OUTER JOIN
    	dbo.SLP05 SLP05_1 
    ON
    	dbo.SLP15_DN.Account = SLP05_1.Account
    FULL OUTER JOIN
    	dbo.Ledgers Ledgers_1
    ON
    	SLP05_1.CRSP05 = Ledgers_1.CRSP05
    FULL OUTER JOIN
    	dbo.QueryProductLines
    LEFT OUTER JOIN
    	dbo.QueryTypes
    ON
    	dbo.QueryProductLines.QueryTypeID = dbo.QueryTypes.QueryTypeID
    LEFT OUTER JOIN
    	dbo.PassedTo
    ON
    	dbo.QueryProductLines.PassedToID = dbo.PassedTo.PassedToID
    LEFT OUTER JOIN
    	dbo.UOM
    ON
    	dbo.QueryProductLines.UOMID = dbo.UOM.UOMID
    ON
    	dbo.SLP15_DN.DebitReference = dbo.QueryProductLines.DebitReference
    ON 
    	dbo.SLP15_IN.InvoiceReference = dbo.QueryProductLines.InvoiceReference
    WHERE
    	(dbo.QueryProductLines.ThisPartCleared = 0)
    AND
    	(dbo.SLP15_IN.OutstandingAmount <> 0)
    OR
    	(dbo.QueryProductLines.ThisPartCleared = 0)
    AND
    	(dbo.SLP15_DN.OutstandingAmount <> 0)
    ORDER BY
    	InvoiceDate
    NB
    All I have done is reformat the text - the names are as it was created.
    An explanation for the table names would probably be useful:
    SLP05 - Customer names and addresses
    SLP15_IN - Sales ledger invoice details
    Ledgers - Credit controller assignations
    SLP15_DN - Sales ledger debit notes details
    QueryProductLines - Enquiry SKU details
    QueryTypes - Enquiry types
    PassedTo - Information on who's dealing with it

    So, can anyone unravel this? I tried to construct a diagram of the links, and got lost, but I can have a go at scanning it and uploading it if that would help.

    As far as I can see, the important tables for the sake of this problem are QueryProductLines and SLP15_DN - the former holds the product codes being queried, and the latter holds the total amount of the claim. The debit note in question has a record in both tables, yet it does not appear in the view's output. Can a fresh pair of eyes spot what I'm missing?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    With all of the outer joins, is it possible that the where clause is having to process a null record, and rejecting it? I would probably start by checking through the tables (especially the ones in the WHERE clause) to make sure all the related data has been entered, and something is not missing.

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Ah, that was the other thing that I forgot to mention. Without parentheses, I have a very hard time interpreting how the where clause will work! Am I right in thinking that it will return records that show QueryProductLines.ThisPartCleared = 0 and (SLP15_IN.AmountOutstanding <> 0 or SLP15_DN.AmountOutstanding <>0)?

    Thanks for getting through all that!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am pretty sure that the priority in the order of operations for "and" and "or" are the same, so yes, it does work out to that in the end. Where the application is not getting updated, my money is on one of those joins returning a null row.

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Okay, thanks. I'll check the debit notes table as I've seen the records in QueryProductLines!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Have (more or less) worked out what's going on with this one, thanks!

    The debit note has three lines. Two of them are flagged as cleared, and so will be excluded from the view. It doesn't have a related record in SLP15_DN, and its related invoice has no balance outstanding, so the third record is also excluded.

    No I just need to explain that to a user...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I too am not sure how SQL Server would evaluate that mess-of-where-clause, but I can see that what the coder intended was this:

    Code:
    WHERE	(dbo.queryproductlines.thispartcleared = 0
    	AND dbo.slp15_in.outstandingamount <> 0)
    	OR
    	(dbo.queryproductlines.thispartcleared = 0
    	AND dbo.slp15_dn.outstandingamount <> 0)
    ...which can actually be simplified to this:
    Code:
    WHERE	dbo.queryproductlines.thispartcleared = 0
    	AND (dbo.slp15_in.outstandingamount <> 0 OR dbo.slp15_dn.outstandingamount <> 0)
    You should at least make this code change first to see if it solves the problem, and then start diving down into those nested full outer joins.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Thanks! I do have a mini-project to go through all SQL objects and reformat them where possible, and I will make this change then.

    The problem was eventually traced back to a duplicate record in the core system, and that' under investigation by the vendors. It's out of my hands, so I can get on with the 1001 other things that have cropped up in the mean time...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •