Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    2

    Talking Unanswered: Query LAG/LEAD problem

    Hello
    I am new to database stuff

    To make it simple i have one table with dates.
    Validto
    11-01-2010
    10-10-2010
    null

    i have to insert into another table but i have to get VALIDFROM IN QUERY
    ValidFrom VALIdto
    01-01-1900 11-01-2010
    12-01-2010 10-10-2010
    11-10-2010 1.1.2499 <- heres a problem

    how can i set this value ? Iv been using lag function and my query is like this
    Code:
    select  scenarioid,formulaid,
    lag (validto+1,1,'1-january-1900') over (order by validto) as validfrom,validto FRom table1
    order by validto;
    I think i could use lead function and set default date to 1.1.2499 but it will mess my first lag query ...

    tnx for suggestions or help
    Benjamin

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    Use case expressions
    Code:
    select lag (validto+1,1,to_date('1-1-1900','dd-mm-yyyy')) over (order by validto) as validfrom,
           case when validto is null
                then to_date('1-1-2499','dd-mm-yyyy')
                else validto
           end validto
    from table1
    order by validto;
    You can also use NVL and DECODE functions, but they are oracle specific and wouldn't work on other databases
    Code:
    select lag (validto+1,1,to_date('1-1-1900','dd-mm-yyyy')) over (order by validto) as validfrom,
           nvl( validto, to_date('1-1-2499','dd-mm-yyyy')) validto,
           decode( validto, null, to_date('1-1-2499','dd-mm-yyyy'), validto ) validto2
    from table1
    order by validto;

  3. #3
    Join Date
    May 2012
    Posts
    2
    kordiko thanx !

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •