Home » SQL & PL/SQL » SQL & PL/SQL » lag and lead only for numbers ?
lag and lead only for numbers ? [message #424487] Fri, 02 October 2009 11:47 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
I am trying to use analytical functions for dates,

CREATE TABLE SAM(ID number, PVAL varchar2(40),NEWVAL varchar2(40), U_TM date)

INSERT INTO SAM VALUES(1,'P','E',to_date('04/20/2009','MM/DD/YYYY'))
/
INSERT INTO SAM VALUES(2,'E','PP',to_date('05/26/2009','MM/DD/YYYY'))
/
INSERT INTO SAM VALUES(3,'PP','P',to_date('05/28/2009','MM/DD/YYYY'))
/
ALTER TABLE SAM ADD(DCOL NUMBER)
/


IF you give this

SELECT * FROM SAM order by u_tm

the new column dcol should show the number of days between
the current row and previos row for u_tm column

Re: lag and lead only for numbers ? [message #424488 is a reply to message #424487] Fri, 02 October 2009 11:58 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
ora1980 wrote on Fri, 02 October 2009 18:47

the new column dcol should show the number of days between
the current row and previos row for u_tm column


That makes no sense. Just use lag/lead in the query (It works with dates), don't store the days between in an additional column that won't get updated if the data changes.
Re: lag and lead only for numbers ? [message #424491 is a reply to message #424487] Fri, 02 October 2009 12:36 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>I am trying to use analytical functions for dates,
Really? I guess we will have to take your word for that.

If you are patient, I am sure that somebody will spoon feed you a solution as was the case to your previous challenge.


http://www.orafaq.com/forum/m/424201/136107/#msg_424196
Re: lag and lead only for numbers ? [message #424492 is a reply to message #424487] Fri, 02 October 2009 13:27 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
2 things

1. in my previous thread, i did give a perfect working plsql
block which did the work, so wonder what spoon feeding meant

2. here is the solution for this problem

merge into sam src using ( select id,
       pval,
       newval,
       u_tm,
       u_tm - lag(u_tm) over (order by u_tm) prev_tm
from   sam) dat
on(src.id =dat.id)
when matched then update set dcol = prev_tm
    


but thanks for giving me the original idea, i just used that
here...

Re: lag and lead only for numbers ? [message #424493 is a reply to message #424492] Fri, 02 October 2009 14:06 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>here is the solution for this problem

What happens to the "solution" when any of the following occur?

INSERT INTO SAM VALUES(4,'E','PP',to_date('05/02/2009','MM/DD/YYYY'));
DELETE FROM SAM WHERE ID = 2;
UPDATE SAME SET U_TM = TO_DATE('04/01/2009','MM/DD/YYYY') WHERE ID = 3;
COMMIT;

Re: lag and lead only for numbers ? [message #424512 is a reply to message #424487] Fri, 02 October 2009 19:42 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
i am sure nobody will delete, or update the table, it is only
for viewing.
Re: lag and lead only for numbers ? [message #424513 is a reply to message #424512] Fri, 02 October 2009 20:04 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
ora1980 wrote on Fri, 02 October 2009 17:42
i am sure nobody will delete, or update the table, it is only for viewing.


Two comments about your response.
You have NO control over potential DELETE or UPDATE in the future.
To ignore the ticking time bomb is foolish when you have been warned about it.

You did not address the INSERT which make your "solution" provide incorrect data.

The solution is a view containing a pseudo column that computes the values on the fly.
Re: lag and lead only for numbers ? [message #424581 is a reply to message #424487] Sun, 04 October 2009 00:02 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
i am struck at this place...can anyone please suggest
a solution ?

please recreate the table with this script

CREATE TABLE SAM(ID number, sid number,tname varchar2(20), cname varchar2(20),
PVAL varchar2(40),NEWVAL varchar2(40), U_TM date);

INSERT INTO SAM VALUES(1,100,'table1','col1','P','E',to_date('04/20/2009','MM/DD/YYYY'))
/
INSERT INTO SAM VALUES(2,200,'table1','col2','E','PP',to_date('05/26/2009','MM/DD/YYYY'))
/
INSERT INTO SAM VALUES(3,300,'table1','col2','PP','P',to_date('05/28/2009','MM/DD/YYYY'))
/
INSERT INTO SAM VALUES(3,300,'table1','col2','P','ee',to_date('05/28/2009','MM/DD/YYYY'))
/
INSERT INTO SAM VALUES(3,300,'table1','col3','rr','cc',to_date('03/28/2009','MM/DD/YYYY'))
/
ALTER TABLE SAM ADD(DCOL NUMBER)

/



if you give this query


select * from sam order by id, sid,tname,cname,u_tm

the new column dcol should show the number of days between
the current row and next row for u_tm column BUT, dcol should
have null whenever cname changes in the table...

i used this merge stmt

merge into sam  src
using (select id
             , sid
              ,pval
          ,newval
          ,u_tm
          ,abs(round((u_tm - lead(u_tm) over (partition by tname,cname order by id,sid, tname,cname,u_tm )),2))  prev_tm
from   sam) dat
on (src.id = dat.id and src.sid = dat.sid )
when matched then update set dcol = prev_tm



is working fine but is not showing null whenever cname changes to
col2 to col3, or if col3 changes back to col1

[Updated on: Sun, 04 October 2009 01:43] by Moderator

Report message to a moderator

Re: lag and lead only for numbers ? [message #424582 is a reply to message #424581] Sun, 04 October 2009 00:20 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
Your whole approach is flawed.
It does not handle dynamic changes to table data.
Any DML after your code runs, can result in bogus data/results.
Re: lag and lead only for numbers ? [message #424583 is a reply to message #424487] Sun, 04 October 2009 00:32 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
sorry i forgot id should be unique,

please consider below

CREATE TABLE SAM(ID number, sid number,tname varchar2(20), cname varchar2(20),
PVAL varchar2(40),NEWVAL varchar2(40), U_TM date);

INSERT INTO SAM VALUES(1,100,'table1','col1','P','E',to_date('04/20/2009','MM/DD/YYYY'))
/
INSERT INTO SAM VALUES(2,200,'table1','col2','E','PP',to_date('05/26/2009','MM/DD/YYYY'))
/
INSERT INTO SAM VALUES(4,300,'table1','col2','PP','P',to_date('05/28/2009','MM/DD/YYYY'))
/
INSERT INTO SAM VALUES(5,300,'table1','col2','P','ee',to_date('05/28/2009','MM/DD/YYYY'))
/
INSERT INTO SAM VALUES(6,300,'table1','col3','rr','cc',to_date('03/28/2009','MM/DD/YYYY'))
/
ALTER TABLE SAM ADD(DCOL NUMBER)

/


the select inside merge works fine...


the idea is, dcol should hold the duration of days a particular column has a value..whenver column name changes, there
should be a null for that row..

i know the requirements are kind of weird, but the whole
scenario is a lot more that this..right now this is what i am
trying to do..

do you think something wrong with merge ? i know the select
works fine..

[Updated on: Sun, 04 October 2009 01:43] by Moderator

Report message to a moderator

Re: lag and lead only for numbers ? [message #424584 is a reply to message #424487] Sun, 04 October 2009 00:36 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
Please assume for a while that no dml will ever occur on this
table

what puzzles me is, the select with the lead works fine,
but merge has a problem, I kno i am repeating many times that
select works, but merge doesnt because i am very puzzled why
this is happening
Re: lag and lead only for numbers ? [message #424585 is a reply to message #424584] Sun, 04 October 2009 00:47 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>Please assume for a while that no dml will ever occur on this table

Please explain how rows got into table.
Then you can not UPDATE table.

Re: lag and lead only for numbers ? [message #424586 is a reply to message #424487] Sun, 04 October 2009 00:48 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
the reason why i say this is that sam is a new log table

the source for sam --> table A, which itself is another log table

source for table A --> is table C

how is table A populated ? --> no matter what column is updated in table C, a trigger populates table A with all
the columns from table c, table A has old values for the change

i was supposed to create new log table sam, load all the historical data into sam, from table A, and table C.

now dcol should have the duration of how long a particular column holded a particular value,
and it should be column specific, ..so whenever column changes, it should show null
Re: lag and lead only for numbers ? [message #424587 is a reply to message #424487] Sun, 04 October 2009 00:50 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
you see this select gives perfect result


select id
             , sid
              ,pval
          ,newval
          ,u_tm
          ,abs(round((u_tm - lead(u_tm) over (partition by tname,cname order by id,sid, tname,cname,u_tm )),2)) from sam


please issue the above after creating the updated script
posted by me.
Re: lag and lead only for numbers ? [message #424588 is a reply to message #424487] Sun, 04 October 2009 00:59 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
i cannot give the calculation of duration (dcol),that goes from one column to another....

i am thinking if there is a way to add to merge whic would
say, do the merge if current row cname = next row cname

Re: lag and lead only for numbers ? [message #424589 is a reply to message #424487] Sun, 04 October 2009 01:04 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
tried this

merge into sam  src
using (select id
             , sid
              ,pval
          ,newval
          ,u_tm
          ,abs(round((u_tm - lead(u_tm) over (partition by tname,cname order by id,sid, tname,cname,u_tm )),2))  prev_tm
from   sam) dat
on (src.id = dat.id and src.sid = dat.sid )
when matched then update set dcol = prev_tm
where cname = lead(u_tm) over (partition by tname,cname order by id, sid, cname,u_tm


but window functions not allowed in where clause
Re: lag and lead only for numbers ? [message #424591 is a reply to message #424589] Sun, 04 October 2009 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post what should be the result with the data you gave.

Regards
Michel
Re: lag and lead only for numbers ? [message #424594 is a reply to message #424487] Sun, 04 October 2009 06:57 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
This should be the result

SQL> select id
  2               , sid
  3  ,cname
  4                          ,u_tm
  5            ,abs(round((u_tm - lead(u_tm) over (partition by tname,cname order by id,sid, tname,c
name,u_tm )),2)) as dcol from sam;

        ID        SID CNAME                U_TM            DCOL
---------- ---------- -------------------- --------- ----------
         1        100 col1                 20-APR-09
         2        200 col2                 26-MAY-09          2
         4        300 col2                 28-MAY-09          0
         5        300 col2                 28-MAY-09
         6        300 col3                 28-MAR-09



when the cname changes, we want null for dcol
Re: lag and lead only for numbers ? [message #424601 is a reply to message #424487] Sun, 04 October 2009 07:53 Go to previous message
ora1980
Messages: 251
Registered: May 2008
Senior Member

SQL> update sam set dcol = (
  2                         select diff_date from (select id,abs(round(u_tm - 
  3                                                                ( lead(u_tm) over (partition by t
name, cname order by ID, sid, tname, cname, u_tm)),2)) diff_date
  4                                                        from sam
  5                                                       ) t1 
  6                                      where t1.id= sam.id );

5 rows updated.

SQL> commit;

Commit complete.

SQL> select id, sid,cname,u_tm,dcol from sam order by tname,cname,u_tm;

        ID        SID CNAME                U_TM            DCOL
---------- ---------- -------------------- --------- ----------
         1        100 col1                 20-APR-09
         2        200 col2                 26-MAY-09          2
         4        300 col2                 28-MAY-09          0
         5        300 col2                 28-MAY-09
         6        300 col3                 28-MAR-09




this works, but for some reason runs slower on my table which has
over a million rows
Previous Topic: trigger needed
Next Topic: get columns consisting of only numbers
Goto Forum:
  


Current Time: Wed Sep 28 17:44:48 CDT 2016

Total time taken to generate the page: 0.17192 seconds