Re: Help with complex SQL
Date: Wed, 4 Sep 2002 16:55:30 -0500
Message-ID: <al5u1m$23s$1_at_localhost.localdomain>
What Jim suggested is a better way. But if you insist, this can also be obtained using SQL*Plus in the following way.
create table a(b date, c varchar2(60));
insert into a values('01-DEC-2002', '{01-JAN-2002 10, 01-FEB-2002 20,
01-JAN-2003 30}');
The following query will give you the greatest date based on your
requirement.
select greatest(greatest((greatest(b, to_date(substr(c, 2, instr(c, ' ', 1) - 1 )))), (to_date(substr(c, (instr(c, ',', 1) + 2), ((instr(c, ' ', (instr(c, ',', 1) + 2), 1)) - (instr(c, ',', 1) + 2)))))), (to_date(substr(c, (instr(c, ',', 2, 2) + 2), ((instr(c, ' ', (instr(c, ',', 2, 2) + 2), 1)) - (instr(c, ',', 2, 2) + 2)))))) from a;
HTH,
Prasad
"Naeem" <naeem.saleem_at_mandg.co.uk> wrote in message
news:f223e72a.0209040321.67707d05_at_posting.google.com...
> Hi all,
>
>
> Have the following two column in a table:-
>
> Maturity_date Schedule
> 01-DEC-2002 {01-JAN-2002 10,01-FEB-2002 20,01-JAN-2003 30}
>
> A date List of Dates and amounts with comma seperator.
>
> If any of the dates in the Schedule column are greater then the
> Maturity_date then I want to ignore them ... i.e. 01-JAN-2003 30 in
> the above example.......
>
> What is the easiest way of achieving this in SQL?
>
> Many thanks in advance.
Received on Wed Sep 04 2002 - 23:55:30 CEST