[AccessD] Stored Procedure not producing results

David Emerson newsgrps at dalyn.co.nz
Tue Nov 21 19:16:54 CST 2006


SQL 2000

I have the following query running in Query Analyser:

SELECT dbo.tblCustStatement.StatementID, dbo.tblCustomers.TradingName 
as CustName, dbo.tblCustStatement.StatementDate,
         dbo.tblCustomers.AccountNo, dbo.tblCustStatement.StatementNumber
FROM dbo.tblCustomers INNER JOIN dbo.tblCustICP ON 
dbo.tblCustomers.CustomerID = dbo.tblCustICP.CustIDNo
         INNER JOIN dbo.tblCustStatement ON 
dbo.tblCustomers.CustomerID = dbo.tblCustStatement.CustIDNo
         LEFT OUTER JOIN dbo.vwAgedDebtorReceipt ON 
dbo.tblCustStatement.CustIDNo = dbo.vwAgedDebtorReceipt.CustIDNo
         AND dbo.tblCustStatement.StatementNumber = 
dbo.vwAgedDebtorReceipt.StatNum
         LEFT OUTER JOIN dbo.vwAgedDebtorReceiptInv ON 
dbo.tblCustStatement.CustIDNo = dbo.vwAgedDebtorReceiptInv.CustIDNo
         AND dbo.tblCustStatement.StatementNumber = 
dbo.vwAgedDebtorReceiptInv.InvNum
WHERE
         -- Remming out this statement works (and removing the AND at 
the beginning of the next line)
         (dbo.tblCustStatement.StatementDate >= '2006-11-01' And 
dbo.tblCustStatement.StatementDate <= '2006-11-30')
         -- Remming out this statement works
         AND (dbo.tblCustomers.AccStatus= 'Active')
         AND (dbo.tblCustStatement.[CurrentMth]+ 
dbo.tblCustStatement.[OneMonth]+ 
dbo.tblCustStatement.[TwoMonths]+ 
dbo.tblCustStatement.[ThreeMonths] + 
ISNULL(dbo.vwAgedDebtorReceipt.NetReceipts,0) 
+            ISNULL(dbo.vwAgedDebtorReceipt.GJCredit,0)+ 
ISNULL(dbo.vwAgedDebtorReceiptInv.NetReceiptsInv,0) 
+               ISNULL(dbo.vwAgedDebtorReceiptInv.GJCreditInv,0) > 20)
         AND (dbo.tblCustomers.PayArrange = 0)
         AND (dbo.tblCustomers.NoOverdueNote = 0)
         -- Remming out this statement works
         AND (   CASE
                         WHEN 
ISNUMERIC(LEFT(dbo.tblCustomers.CreditStatus,2)) = 0 THEN '00'
                         ELSE LEFT(dbo.tblCustomers.CreditStatus,2)
                 END < CAST(DAY(GETDATE()) AS varchar(2)))
         AND (dbo.tblCustomers.CreditStatus NOT LIKE 'Last%' OR 
dbo.tblCustomers.CreditStatus IS NULL)
         -- Remming out this statement works
         AND (dbo.tblCustStatement.StatementNumber <> 1)
ORDER BY CustName, dbo.tblCustomers.AccountNo, 
dbo.tblCustStatement.StatementDate

When I remark out any 1 of the AND statements noted above in the 
WHERE clause the results come back in a couple of seconds.
However, when I include all AND statement the query runs doesn't 
return any results (at least not within the first ten minutes!)

There doesn't seem to be any pattern to the lines that can be remmed 
or combinations of remaining lines.  The only clue I have is that 
data from the database from two months ago works fine but there are 
750 more records in each table being filtered.

Any suggestions as to how I can solve this - it seems to be a data 
issue but the various combinations of AND statements above doesn't make sense.





More information about the AccessD mailing list