-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|