I'm working on the following query on Sybase ASE 12.5.4:

select distinct t2.pay_method_number "Cnumber",
t1.order_number,
t4.ship_company_name,
t2.method_of_payment,
t5.amount 'GC Amt$'

from order_header t1, payment_header t2, shipping_detail t3, shipping_header t4, payment_detail t5

where t1.order_number = t2.order_number
and t1.order_number = t4.order_number
and t2.order_number=t5.order_number
and t2.payment_number=t5.payment_number
and t3.date_shipped >= dateadd(dd,-1,"11/15/11")
and t3.date_shipped < dateadd(dd,0,"11/15/11")
and t1.order_number=t3.order_number
and t1.source_key = "RNWHUMC"
and t1.order_status not in ("H", "SH", "DE")
and t2.method_of_payment = "GC"
and t1.customer_number != XXXXXX

when in this form it returns the proper record in 1 sec.

if I change the following from:
and t3.date_shipped >= dateadd(dd,-1,"11/15/11")

and make it:
and t3.date_shipped >= dateadd(dd,-1,getdate())

the query never returns. Show plan using the "11/15/11" show indexes being used on all the tables. When I change the date to getdate() the plan shows a table scan on a very large table. And it's not the t3 table, but the t1 table.

Any insight is greatly appreciated.
Eric

When last did you update statistics?
Try running "update index statistics" on the tables

PS. Your query might not work as expected as getdate() include time, I suggest truncate the time with
dateadd(dd, datediff(dd, '' ,getdate()), '')
dateadd(dd, datediff(dd, '' ,getdate())-1, '')

Thanks for the information. I did find out that running it on 12.5.4 using a function or variable is most likely to get a table scan.

I ran the query using getdate() on 15.5 (test server) and it ran very fast, and did not perform the scan.

I will try the update index stats since I have to get this into production on 12.5.4.

Thanks again,
eric

Using a function should not be a problem as long as it is not on the indexed column
i.e. this is OK But this can't use an indexI still suggest you update index statistics on ALL tables involved.
As a 2nd option try recreating the indexes or reorg rebuild the indexes.