Row_number(0 function not working as expected

Database

Row_number(0 function not working as expected

Hi

I am using row number function in my select and s=inserting the resultset into some other table. But it is not working as expectedand am missing soem records in new table.

Here is my select sql:

select clr_id, id,station , c_date,permfeed,clr_status_key,1

from (

select s.show_event_id id , clr.clearance_event_id clr_id,clr.changed_date c_date, adefs.feed_no permfeed,days_of_week as max_days_of_week,clr.station_key station,s.broadcast_date bdate,stat.clearance_status_key clr_status_key

,(SELECT

case when 6-(day_of_week mod 7) =6 then 2*2*2*2*2*2

when 6-(day_of_week mod 7) =5 then 2*2*2*2*2

when 6-(day_of_week mod 7) =4 then 2*2*2*2

when 6-(day_of_week mod 7) =3 then 2*2*2

when 6-(day_of_week mod 7) =2 then 2*2

when 6-(day_of_week mod 7) =1 then 2

when 6-(day_of_week mod 7) =0 then 1 end FROM sys_calendar.calendar WHERE calendar_date = CAST(s.broadcast_date AS DATE FORMAT 'YYYYMMDD')-1) as day_of_week

,((CAST (COALESCE(adefs.valid_until, 22000101) AS DATE FORMAT 'YYYYMMDD')-

CAST(adefs.valid_from AS DATE FORMAT'yyyyMMdd'))* 2 ) + CASE WHEN act.action_code ='R' THEN 0 ELSE 1 END max_range

, CASE WHEN (

(COALESCE(s.feed_no, -1) <> COALESCE(adefs.feed_no, -1))

OR (COALESCE(clr.LEAVE_TIME, 0) <> COALESCE(adefs.lip, 0))

OR (COALESCE(clr.join_time, 0) <> COALESCE(adefs.jip, 0))

OR (COALESCE(cst.clearance_status_key, -1) <> COALESCE(stat.clearance_status_key, -1))

) THEN 1 ELSE NULL

END EXCEPTIONS

,max(MAX_RANGE) over (partition by s.basis_no,clr.station_key,s.BROADCAST_DATE) drange

, row_number() over (partition by s.basis_no,clr.station_key,s.BROADCAST_DATE ORDER BY EXCEPTIONS DESC ) AS ROWNUM1

from shows s,clearance clr,clearance_defs adefs,clearance_defs_action act, clearance_status stat , CLEARANCE_status cst

where

s.SHOW_EVENT_ID=clr.SHOW_EVENT_ID

and s.basis_no=adefs.basis_no

and adefs.STATION_KEY=clr.station_key

and adefs.clearance_action_id=act.CLEARANCE_ACTION_ID

and ACTION_CODE<>'R'

AND(

( s.broadcast_date <= adefs.valid_from AND adefs.valid_from <= COALESCE(s.broadcast_date, 99999999))

OR

(s.broadcast_date<= COALESCE(adefs.valid_until, 99999999) AND COALESCE(adefs.valid_until, 99999999) <= COALESCE(s.broadcast_date, 99999999))

OR (s.broadcast_date<= adefs.valid_from AND COALESCE(s.broadcast_date, 99999999) >= COALESCE(adefs.valid_until, 99999999))

OR

(adefs.valid_from <= s.broadcast_date AND COALESCE(adefs.valid_until, 99999999) >= COALESCE(s.broadcast_date, 99999999))

)

AND adefs.valid_until - adefs.valid_from >= 0

AND adefs.delete_indicator = '0'

AND clr.delete_indicator = 0

AND s.delete_indicator = 0

AND s.active_indicator= 1

and s.broadcast_date between adefs.VALID_FROM and adefs.VALID_UNTIL

AND TD_SYSFNLIB.BITAND(CAST(max_days_of_week AS INTEGER), day_of_week) > 0

and stat.clearance_status_key = adefs.clearance_status_key

and cst.clearance_status_key = clr.clearance_status_key

--and clr.clearance_event_id=144553366

and clr.station_key=977

--qualify row_number() over (partition by s.basis_no,clr.station_key,s.BROADCAST_DATE ORDER BY EXCEPTIONS DESC ) =1

)a

where rownum1=1

insert stmt:

Insert into ABC (CLEARANCE_EVENT_ID,SHOW_EVENT_ID,STATION_KEY,CHANGED_DATE,PERM_FEED,PERM_CLEARANCE_STATUS_KEY,active_ind)