The same song my_interval VARCHAR(1) := 7; my_limit_date DATE;
begin ... select (TRUNC(sysdate)-TO_NUMBER(my_interval)+1) into my_limit_date from dual; ... sql_stmt := 'SELECT * FROM my_table WHERE .... :1 AND erh.arrival_dt < :2 AND... '; EXECUTE IMMEDIATE sql_stmt USING my_first, my_limit_date ... ; JP On Thu 15. November 2001 17:24, [EMAIL PROTECTED] wrote: > Jan, > > Thanks for this reply. What I forgot to mention is that this 7 is a > literal > that I substituted for a bind variable :b2. > > Sorry about leaving out that critical piece of information. > > Cherie > > > > Jan Pruner > <[EMAIL PROTECTED] To: [EMAIL PROTECTED] > > > cc: [EMAIL PROTECTED] > > Subject: Re: Need help > rewriting SQL with TRUNC function 11/15/01 10:13 > AM > Please respond > to jan > > > > > > > Hmm, > > my_limit_date DATE := trunc(sysdate) - 6; -- !!! only 6 > > erh.arrival_dt < my_limit_date > > > JP > > On Thu 15. November 2001 16:00, you wrote: > > I think that the following statement is keeping this application from > > using > > > an index in my cost-based execution plan. This is an 8.0.4 database so I > > don't have the option of creating a function-based index. > > > > I'm not that great with SQL. Can anyone help me rewrite this statement > > so > > > that I don't need to use the TRUNC function on this column in blue? > > > > (trunc(sysdate) - trunc(erh.arrival_dt)) >= 7 AND > > > > Thanks, > > > > Cherie Machler > > Oracle DBA > > Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
