[AccessD] Stored Procedure not producing results

Thanks for the suggestion, however since we want all the records that 
match the filters I can't see how using TOP will help (unless it is TOP 100)?

David

At 22/11/2006, you wrote:
>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
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com