Re: SQL Statement Help

"Kobee" <dbmdata_at_gmail.com> wrote in message news:1152202945.932701.169190_at_a14g2000cwb.googlegroups.com...
> Oracle 9i
>
> SELECT
> PROGRAM_ID, PROGRAM_TYPE, ANNUALBUDGETTYPE, DESCRIPTION, NAME,
> NVL(SPLIT_FEDERAL, 0) AS "SPLIT_FEDERAL", NVL(SPLIT_MUNICIPAL, 0) AS
> "SPLIT_MUNICIPAL", NVL(SPLIT_PROVINCIAL, 0) AS "SPLIT_PROVINCIAL",
> AFS_FLAG, CAPITALPLAN_FLAG, CER_FLAG, HPEMS_FLAG, START_DATE,
> RENTSUP_FLAG,
> BENCHMARK_FLAG, STATUS, NVL(SUBSIDY_CC, 0) AS "SUBSIDY_CC",
> LASTUPDATE, NVL(SUBSIDY_CE, 0) AS "SUBSIDY_CE",
> DATE_CREATED, TRANSACTION_TYPE, MODIFIED_BY, TRANSACTION_ID,
> TRANSACTION_ORDER
> FROM
> PROGRAM_LOG
> WHERE
> TO_DATE(TO_CHAR(LASTUPDATE, 'yyyy-Mon-dd'), 'yyyy-Mon-dd') BETWEEN
> TO_DATE(dtstartdate, 'yyyy-Mon-dd') AND TO_DATE(dtenddate,
> 'yyyy-Mon-dd')
> OR
> TRANSACTION_TYPE = 'U'
> ORDER BY
> TRANSACTION_ID,
> TRANSACTION_ORDER,
> LASTUPDATE DESC;
>
>
> I' ve tried several approaches, but so far the "OR" statement is coming
> closest to what I need. The only problem is that the OR clause will
> return records whether or not there is a corresponding "Update" row
> within the date range.
>
> Thanks
>
>
> DA Morgan wrote:
> > Kobee wrote:
> > > Hi,
> > >
> > > I've been trying to get this SQL, but I just can't get it to work.
> >
> > Where is your SQL?
> >
> > We can't help you with it if you don't post it.
> >
> > And your Oracle version number?
> > --
> > Daniel A. Morgan
> > University of Washington
> > damorgan_at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Group
> > www.psoug.org
>

Why are you doing:
TO_DATE(TO_CHAR(LASTUPDATE, 'yyyy-Mon-dd'), 'yyyy-Mon-dd') just do
lastupdate between ...

Your conversion of a date to a char to a date might be causing a full table scan where you don't need it.
Jim Received on Thu Jul 06 2006 - 22:58:39 CDT