Re: Update inside cursor loop

On Apr 11, 10:49 pm, "klimen..._at_gmail.com" <klimen..._at_gmail.com> wrote:
> On Apr 11, 2:12 pm, dwmc..._at_southernco.com wrote:
>
>
>
>
>
> > My table has a NUMBER field that has been used as a date field (setup
> > by someone else). 12012006 would be 12/01/2006, 7042006 would be
> > 7/04/2006. I have recently added a DATE field to the table. I am
> > trying to write something that will take the NUMBER, re-format it, and
> > UPDATE the DATE field in the same record. I figured out how to slice
> > up the number into month day and year chunks with the SUBSTR function
> > as long as I do 8 digit and 7 digit numbers seperately. Running two
> > versions of the program will not be a problem. This is what I have so
> > far:
>
> > declare
> > cursor d1 is
> > select fuelfilldate, fuel_fill_dt2 from lincbase.genrawdata
> > where fuel_fill_dt2 is null and length(to_char(fuelfilldate)) = 8
> > for update of fuel_fill_dt2;
> > begin
> > for gen_rec in d1 loop
> > gen_rec.fuel_fill_dt2 :=
> > to_date(substr(gen_rec.fuelfilldate,-8,2) || '/' ||
> > substr(to_char(gen_rec.fuelfilldate),-6,2) || '/' ||
> > substr(to_char(gen_rec.fuelfilldate),-4),'mm/dd/yyyy');
> > end loop;
> > end;
> > /
>
> > It runs without errors however it does not appear to actually do
> > anything. The fuelfilldate field is the NUMBER, the fuel_fill_dt2 is
> > the DATE field.
>
> > Thanks,
>
> > Danny
>
> Danny,
> What people trying to tell you is that you probably need this -
>
> update lincbase.genrawdata set fuel_fill_dt2 =
> to_date(substr(fuelfilldate,-8,2) || '/' ||
> substr(to_char(fuelfilldate),-6,2) || '/' ||
> substr(to_char(fuelfilldate),-4),'mm/dd/yyyy')
> where fuel_fill_dt2 is null and length(to_char(fuelfilldate)) = 8;
>
> -
> Roman
> Sr.DBA (Oracle/SQL)- Hide quoted text -
>
> - Show quoted text -

THANKS! That worked great. I was obviously making it alot harder than I needed to.

-Danny Received on Thu Apr 12 2007 - 10:48:53 CDT