réintegrarion des numeros d'operation dans station

[cedric]

select * from wrbd.fish_fi left join (select * from wrbd.operation_op where op_nbtotal>0)as ope 
on (fi_date,fi_st_id)=(ope.op_date,ope.op_st_id)  
except (
select distinct on (fi_id) * from(
select * from wrbd.fish_fi left join (select * from wrbd.operation_op where op_nbtotal>0)as ope 
on (fi_date,fi_st_id)=(ope.op_date,ope.op_st_id) ) as sub)
 --2357
alter table wrbd.fish_fi add column fi_op_id integer;
-- generation du script d'insert à sauvegarder dans fish_update.txt (je passe au inner join pour ne pas avoir de lignes vides)
-- il faut ensuite remplacer "" par ' et " par rien dans notepad.
-- il doit y avoir encore mieux mais je ne vois pas
select *
 from wrbd.fish_fi join (select * from wrbd.operation_op where op_nbtotal>0)as ope 
on (fi_date,fi_st_id)=(ope.op_date,ope.op_st_id) 

select 'update wrbd.fish_fi set fi_op_id= ' ||cast(op_id as text)||' where fi_st_id="'||cast(fi_st_id as text)||'";'
 from wrbd.fish_fi join (select * from wrbd.operation_op where op_nbtotal>0)as ope 
on (fi_date,fi_st_id)=(ope.op_date,ope.op_st_id) 
order by fi_id ;  

[cedric]

there was a problem at first with operations dates for joining with the operation table.
OK everything solved use sql script

[cedric]

Corrected script

select 'update wrbd.fish_fi set fi_op_id= '

cast(op_id as text)

' where fi_id="'

cast(fi_id as text)

'";'

from (select * from wrbd.fish_fi ) fi

join (select * from wrbd.operation_op where op_nbtotal>0 and op_folio_no is not null )as ope

on (fi_date,fi_st_id)=(ope.op_date,ope.op_st_id)
order by fi_id ;

[cedric]

Milestone Data integration deleted