asked on

Problem updating selected rows (with rank)

HI i need to convert this to an update and i have no success :(
Here is the select

select rn, rdt, id_cpt_inet, no_tel, dt_deb, dt_fin, dt_com, dt_eff_inet, cd_stat_approv, no_tp_compo
from (
select
rank() over (
partition by no_tel, id_cpt_inet
order by
nvl (dt_com, to_date(19000101,'yyyymmdd')) desc,
nvl (dt_fin, to_date(20090101,'yyyymmdd')) desc,
nvl (dt_deb, to_date(20090101,'yyyymmdd')) desc
)
rn, rdt, id_cpt_inet, no_tel, dt_deb, dt_fin, dt_com, dt_eff_inet, cd_stat_approv, no_tp_compo
from (
      select
      rank() over (
            partition by id_cpt_inet
            order by dt_eff_inet desc
      )
      rdt, id_cpt_inet, no_tel, dt_deb, dt_fin, dt_com, dt_eff_inet, cd_stat_approv, no_tp_compo
      from cleaned_sga_tpc
) where rdt =1
) where rn=1 and cd_stat_approv != 8 and cd_stat_approv != 1 and no_tp_compo = 11

I need to update the table cleaned_sga_tpc and for each of those record set it to the same value (1 for exemple), basically tagging my good records selected.

I tried with a select for update in a cursor and it seems to not like the analytics commands :(

Sujith

Try this :
update cleaned_sga_tpc X
set <your col> = (select rn FROM
(
select rn, r_id
from (
select
rank() over (
partition by no_tel, id_cpt_inet
order by
nvl (dt_com, to_date(19000101,'yyyymmdd')) desc,
nvl (dt_fin, to_date(20090101,'yyyymmdd')) desc,
nvl (dt_deb, to_date(20090101,'yyyymmdd')) desc
)
rn, r_id, rdt, id_cpt_inet, no_tel, dt_deb, dt_fin, dt_com, dt_eff_inet, cd_stat_approv, no_tp_compo
from (
select
rowid rid,
rank() over (
partition by id_cpt_inet
order by dt_eff_inet desc
)
rdt, id_cpt_inet, no_tel, dt_deb, dt_fin, dt_com, dt_eff_inet, cd_stat_approv, no_tp_compo
from cleaned_sga_tpc
) where rdt =1
) where rn=1 and cd_stat_approv != 8 and cd_stat_approv != 1 and no_tp_compo = 11
)
where r_id = X.rowid )
/

beaudoin_n

ASKER

doesn't work.. tells me r_id is invalud identifier ...

ASKER CERTIFIED SOLUTION

Sujith

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial