MyTetra Share
Делитесь знаниями!
Пример
Время создания: 29.01.2021 10:20
Раздел: INFO - JOB - rtneo - Работа над задачами - Загрузка долей
Запись: wwwlir/Tetra/master/base/1611886845ay3vxxwel9/text.html на raw.githubusercontent.com

BEGIN WORK;


with shares as (

select

"cre_id"::uuid as id,

"cad" as cad,

round((split_part(share, '/', 1)::bigint)/(split_part(share, '/', 2)::bigint)::numeric, 5) as sh

from

t_dfgv98

where

share SIMILAR TO '[0-9]*/[0-9]*'

order by

"cad")

update

rtneo_contragent_real_estate cre

set

share_ = shares.sh

from

shares

where

cre.id = shares.id


COMMIT WORK;



-- not split_part(share, '/', 1) = ''

-- or not split_part(share, '/', 2) = ''



=============================================================================

CREATE TEMP TABLE t_dfgv98

(

"cre_id" varchar(50),

"cad" varchar(50),

"share" text,

"fio_data" text,

"fio_order" text,

"error" text

);



copy t_dfgv98 from '/home/rtneo/rtneo/data/creShare_set.csv' (delimiter ',', format "csv");


rollback work;

BEGIN WORK;


with shares as (

select t.cre_id::uuid as id, round((split_part(share, '/', 1)::bigint)/(split_part(share, '/', 2)::bigint)::numeric, 5) as sh from rtneo_contragent_real_estate cre join t_dfgv98 t on cre.id = t.cre_id::UUID where cre.share_ is null and (not t.share = '' and t.share is not null) and t.share SIMILAR TO '[0-9]*/[0-9]*'

)

update

rtneo_contragent_real_estate cre

set

share_ = shares.sh

from

shares

where

cre.id = shares.id;


COMMIT WORK;


//Доли для вставки

select t.cre_id::uuid as id, round((split_part(share, '/', 1)::bigint)/(split_part(share, '/', 2)::bigint)::numeric, 5) as sh from rtneo_contragent_real_estate cre join t_dfgv98 t on cre.id = t.cre_id::UUID where cre.share_ is null and (not t.share = '' and t.share is not null) and t.share SIMILAR TO '[0-9]*/[0-9]*'


//Только чистые доли

select cre.id, cre.share_, t.share, round((split_part(share, '/', 1)::bigint)/(split_part(share, '/', 2)::bigint)::numeric, 5) as total_share from rtneo_contragent_real_estate cre join t_dfgv98 t on cre.id = t.cre_id::UUID where cre.share_ is null and (not t.share = '' and t.share is not null) and t.share SIMILAR TO '[0-9]*/[0-9]*';


//Показывает все доли, вместе с грязными

select cre.id, cre.share_, t.share from rtneo_contragent_real_estate cre join t_dfgv98 t on cre.id = t.cre_id::UUID where cre.share_ is null and (not t.share = '' and t.share is not null);

Так же в этом разделе:
 
MyTetra Share v.0.59
Яндекс индекс цитирования