DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Using LEAD analytic function

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    Using LEAD analytic function

    Hi All,

    I have a table which contains program names.
    Each chaneel id has 2 programs:

    col CHANNEL_VENDOR_ID format 9999
    col PROG_SEQ format 99999
    col PROG_NAME format a10
    col START_DATE format a20
    col END_DATE format a20
    select CHANNEL_VENDOR_ID ,PROG_SEQ,PROG_NAME,START_DATE,END_DATE
    from T_VENDOR_PROGRAMS
    where CHANNEL_VENDOR_ID =213;

    CHANNEL_VENDOR_ID PROG_SEQ PROG_NAME START_DATE END_DATE
    ----------------- -------- ---------- -------------------- --------------------
    213 13477 Nir1 06/05/2004 08:00:00 06/05/2004 09:00:00
    213 13478 nir2 06/05/2004 09:00:08 06/05/2004 10:00:00

    I've tried to query in one select (for reports)the first program and its next program,using LEAD function:

    SQL> select PROG_NAME,
    2 LEAD(prog_name,1,TO_CHAR(NULL)) OVER (ORDER BY PROG_SEQ) next_program_name
    3 from T_VENDOR_PROGRAMS
    4 where CHANNEL_VENDOR_ID =213;

    PROG_NAME NEXT_PROGR
    ---------- ----------
    Nir1 nir2
    nir2

    How to fix the query for getting only one row of both programs:

    PROG_NAME NEXT_PROGR
    ---------- ----------
    Nir1 nir2

    Thanks in advance,
    Nir

  2. #2
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    Code:
    select
     prog_name, next_program_name
    from
     (select 
      prog_name,
      lead(prog_name,1,to_char(null)) over (order by prog_seq) next_program_name
      prog_seq, 
      lead(prog_seq) over(order by prog_seq) lc
    from
     t_vendor_programs) i
    where
     i.channel_vendor_id =213 and
     i.channel_vendor_id = i.lc

  3. #3
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi ddrozdov,

    I'm at home now so i'll check it tomorrow.
    Thanks a lot!!
    I'll suppose to convert the query into a view,and the report will be based on this view.

    Bye,
    Nir

Posting Permissions

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


Click Here to Expand Forum to Full Width