[AccessD] Stored Procedure not producing results

How about limiting the returned records with
something like Select TOP  10
I forget the exact syntax for SQL 2000
 like the LIMIT statement in MySQL or WHERE ROWNUM >11
in Oracle or new function ROW_NUMBER()  in SQL 2005


David Emerson wrote:

>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.
>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada