Re: Tricky query

"James Williams" <willjamu_at_mindspring.com> wrote in message news:3b14c63b.6060566_at_nntp.mindspring.com...
> On 30 May 2001 06:19:47 GMT, "wayne" <no_at_email.please.com> wrote:
> dte mm-dd-yy
> tme hhmmss
>
> Oracle 8.1.6.3 on the Sun Solaris hardware platform.
>
> >> The DTE and TME (Don't ask why the developers didn't use DATE)
> >> will be used to determine the age. The ky_premise_no can be duplicates.
>
> The tricky part (to me!) is coming up with the three oldest
> ky_premise_no for each premise. Each order can can go against several
> premises. My charge is to return in a result the three oldest each
> time ordered by completion date.

Use to_date(dte || ' ' || tme, 'mm-dd-yy hhmiss') to conver the fields to date/time format (note that 'mi' stands for minutes in the template). So you can have something like this:

select to_date(dte || ' ' || tme, 'mm-dd-yy hhmiss') composite from source_table order by composite;

The query above will sort by date and time even though dte and tme are varchars. The real downfall is that it will not take advantage of any index unless you create an expression index (I believe they are new with 8.1.5 or 8.1.6) whose index expression is to_date(dte || ' ' || tme, 'mm-dd-yy hhmiss') .

Try it out and see what happens. Received on Wed May 30 2001 - 07:13:31 CDT