SQL HELO PLEASE !!!!!!!!!! 
Author Message
 SQL HELO PLEASE !!!!!!!!!!

Hello all,

I have the following problem
I have column the was incorectly populated(over time)
what I need to do is updqte the expiration_date column  to the correct value
here is my data(i have approxametly 8,000,000 rows to do this on )LN is not an
actual value in the table but only for illistration purposes
what I need to do is update the expiration_date from LN1 to the create_date of
Ln 2
and the expire date of ln2 with the create_date of ln3
ANY HELP IS GREATLY APPRICATED AND THANKS IN ADVANCE

what I have
LN code    create_date             expire_date          status
------- --------------------    ---------------------   ------------
1  1002 07-SEP-2001 14:57:15    22-OCT-2001 07:42:26    N
2  1002 13-SEP-2001 01:01:35    22-OCT-2001 07:42:26    N
3  1002 22-OCT-2001 07:42:26                            Y

What I want to see
LN code    create_date             expire_date          status
------- --------------------    ---------------------   ------------
1  1002 07-SEP-2001 14:57:15    13-SEP-2001 01:01:35    N
2  1002 13-SEP-2001 01:01:35    22-OCT-2001 07:42:26    N
3  1002 22-OCT-2001 07:42:26                            Y

Dan White
programmer/analyst



Sun, 23 May 2004 03:40:14 GMT
 SQL HELO PLEASE !!!!!!!!!!

Hope it'll help you...
Please let me know about results...

-----begin of  script
declare
  -- Replace YOUR_TABLE with name of your table
  -- Decrease number of rows_to_commit if you have a small rollback segment
  -- but pay attention to it will increase time of execution.
  rows_to_commit number:=10000000;
  rows_processed number:=1;
  cursor C1 is
    select * from YOUR_TABLE order by code,create_date desc
      for update;
    prev_code YOUR_TABLE.code%type;
    prev_created YOUR_TABLE.create_date%type;
begin
  while rows_processed>0 loop
    rows_processed:=0;
    prev_code:=9.99e99;
    for i in C1 loop
      if i.code=prev_code then
        if i.expire_date=prev_created then
          null;
        else
          update YOUR_TABLE set expire_date=prev_created, status='N'
            where current of C1;
          rows_processed:=rows_processed+1;
        end if;
      else
              prev_created:=to_date('2999','YYYY');
        if i.expire_date is null and i.status='Y' then
          null;
        else
          update YOUR_TABLE set expire_date=null, status='Y'
            where current of C1;
          rows_processed:=rows_processed+1;
        end if;
        prev_code:=i.code;
      end if;
      exit when rows_processed>=rows_to_commit;
      prev_created:=i.create_date;
    end loop
    commit;
  end loop;
end;
/
-------- end of script

Quote:
> Hello all,

> I have the following problem
> I have column the was incorectly populated(over time)
> what I need to do is updqte the expiration_date column  to the correct
value
> here is my data(i have approxametly 8,000,000 rows to do this on )LN is
not an
> actual value in the table but only for illistration purposes
> what I need to do is update the expiration_date from LN1 to the
create_date of
> Ln 2
> and the expire date of ln2 with the create_date of ln3
> ANY HELP IS GREATLY APPRICATED AND THANKS IN ADVANCE

> what I have
> LN code    create_date             expire_date          status
> ------- --------------------    ---------------------   ------------
> 1  1002 07-SEP-2001 14:57:15 22-OCT-2001 07:42:26 N
> 2  1002 13-SEP-2001 01:01:35 22-OCT-2001 07:42:26 N
> 3  1002 22-OCT-2001 07:42:26                 Y

> What I want to see
> LN code    create_date             expire_date          status
> ------- --------------------    ---------------------   ------------
> 1  1002 07-SEP-2001 14:57:15 13-SEP-2001 01:01:35 N
> 2  1002 13-SEP-2001 01:01:35 22-OCT-2001 07:42:26 N
> 3  1002 22-OCT-2001 07:42:26                 Y

> Dan White
> programmer/analyst



Sun, 23 May 2004 04:59:14 GMT
 SQL HELO PLEASE !!!!!!!!!!
First does this table have a PK ?
The thing is I thought that the theory is that SQL engine doesn't give a
monkey's
ass (or monkey's tits) about the "order" of the placement of the rows
in the result set so hard part for me is: how can you guarrantee
yourself that row(x) should get the updates from row(x+1) ? ie
how do you define the relationship btw row(x) and row(x+1) ?

Robert


Quote:
> Hello all,

> I have the following problem
> I have column the was incorectly populated(over time)
> what I need to do is updqte the expiration_date column  to the correct
value
> here is my data(i have approxametly 8,000,000 rows to do this on )LN is
not an
> actual value in the table but only for illistration purposes
> what I need to do is update the expiration_date from LN1 to the
create_date of
> Ln 2
> and the expire date of ln2 with the create_date of ln3
> ANY HELP IS GREATLY APPRICATED AND THANKS IN ADVANCE

> what I have
> LN code    create_date             expire_date          status
> ------- --------------------    ---------------------   ------------
> 1  1002 07-SEP-2001 14:57:15 22-OCT-2001 07:42:26 N
> 2  1002 13-SEP-2001 01:01:35 22-OCT-2001 07:42:26 N
> 3  1002 22-OCT-2001 07:42:26                 Y

> What I want to see
> LN code    create_date             expire_date          status
> ------- --------------------    ---------------------   ------------
> 1  1002 07-SEP-2001 14:57:15 13-SEP-2001 01:01:35 N
> 2  1002 13-SEP-2001 01:01:35 22-OCT-2001 07:42:26 N
> 3  1002 22-OCT-2001 07:42:26                 Y

> Dan White
> programmer/analyst



Sun, 23 May 2004 06:04:23 GMT
 SQL HELO PLEASE !!!!!!!!!!
The following query gives me the order that I need

select code,create_date, expire_date,status
from dw_project_dim
order by code,create_date


Quote:
> First does this table have a PK ?
> The thing is I thought that the theory is that SQL engine doesn't give a
> monkey's
> ass (or monkey's tits) about the "order" of the placement of the rows
> in the result set so hard part for me is: how can you guarrantee
> yourself that row(x) should get the updates from row(x+1) ? ie
> how do you define the relationship btw row(x) and row(x+1) ?

> Robert



> > Hello all,

> > I have the following problem
> > I have column the was incorectly populated(over time)
> > what I need to do is updqte the expiration_date column  to the correct
> value
> > here is my data(i have approxametly 8,000,000 rows to do this on )LN is
> not an
> > actual value in the table but only for illistration purposes
> > what I need to do is update the expiration_date from LN1 to the
> create_date of
> > Ln 2
> > and the expire date of ln2 with the create_date of ln3
> > ANY HELP IS GREATLY APPRICATED AND THANKS IN ADVANCE

> > what I have
> > LN code    create_date             expire_date          status
> > ------- --------------------    ---------------------   ------------
> > 1  1002 07-SEP-2001 14:57:15 22-OCT-2001 07:42:26 N
> > 2  1002 13-SEP-2001 01:01:35 22-OCT-2001 07:42:26 N
> > 3  1002 22-OCT-2001 07:42:26                 Y

> > What I want to see
> > LN code    create_date             expire_date          status
> > ------- --------------------    ---------------------   ------------
> > 1  1002 07-SEP-2001 14:57:15 13-SEP-2001 01:01:35 N
> > 2  1002 13-SEP-2001 01:01:35 22-OCT-2001 07:42:26 N
> > 3  1002 22-OCT-2001 07:42:26                 Y

> > Dan White
> > programmer/analyst



Sun, 23 May 2004 14:26:55 GMT
 SQL HELO PLEASE !!!!!!!!!!
You didn't answer the crucial Q: First does this table have a PK ?
All those values in CODE column you showed are the same !


Quote:
> The following query gives me the order that I need

> select code,create_date, expire_date,status
> from dw_project_dim
> order by code,create_date



> > First does this table have a PK ?
> > The thing is I thought that the theory is that SQL engine doesn't give a
> > monkey's
> > ass (or monkey's tits) about the "order" of the placement of the rows
> > in the result set so hard part for me is: how can you guarrantee
> > yourself that row(x) should get the updates from row(x+1) ? ie
> > how do you define the relationship btw row(x) and row(x+1) ?

> > Robert



> > > Hello all,

> > > I have the following problem
> > > I have column the was incorectly populated(over time)
> > > what I need to do is updqte the expiration_date column  to the correct
> > value
> > > here is my data(i have approxametly 8,000,000 rows to do this on )LN
is
> > not an
> > > actual value in the table but only for illistration purposes
> > > what I need to do is update the expiration_date from LN1 to the
> > create_date of
> > > Ln 2
> > > and the expire date of ln2 with the create_date of ln3
> > > ANY HELP IS GREATLY APPRICATED AND THANKS IN ADVANCE

> > > what I have
> > > LN code    create_date             expire_date          status
> > > ------- --------------------    ---------------------   ------------
> > > 1  1002 07-SEP-2001 14:57:15 22-OCT-2001 07:42:26 N
> > > 2  1002 13-SEP-2001 01:01:35 22-OCT-2001 07:42:26 N
> > > 3  1002 22-OCT-2001 07:42:26                 Y

> > > What I want to see
> > > LN code    create_date             expire_date          status
> > > ------- --------------------    ---------------------   ------------
> > > 1  1002 07-SEP-2001 14:57:15 13-SEP-2001 01:01:35 N
> > > 2  1002 13-SEP-2001 01:01:35 22-OCT-2001 07:42:26 N
> > > 3  1002 22-OCT-2001 07:42:26                 Y

> > > Dan White
> > > programmer/analyst



Mon, 24 May 2004 03:18:52 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Help - Data Environment andData Report Helo

2. Helo with Indexes!!!

3. Sybase ADO Helo

4. Helo, someone there??

5. Need Helo with Group Bands in Reports

6. Please Please Please need SQL Link for PDOXDOS 4.5

7. emergency please help me please please please

8. APPROACH QUERY-PLEASE PLEASE PLEASE PLEASE HELP

9. PLEASE PLEASE HELP!!! SQL NESTED TRANSACTIONS

10. xmlutable.bmp - Please, please, please...

11. Please, Please, Please I need help working with dates

12. urgent please i beg please i beg please i beg


 
Powered by phpBB® Forum Software