Просмотреть исходный

Кастомный атрибут основных объектов

Значения

SQL - запрос

Текст

select tfs."value" as v
from tn_field_string tfs
join tn_field tf on tf.id = tfs.id
where tf.owner = rkkbase.id and tf.owner_type = rkkbase.id_type and tf.cmjfield = '{storage-field name}'

Число

select tfd."value" as v
from tn_field_decimal tfd
join tn_field tf on tf.id = tfd.id 
where tf.owner = rkkbase.id and tf.owner_type = rkkbase.id_type and tf.cmjfield = '{storage-field name}'

Дата

select tfs."value" as v
from tn_field_dateonly tfd
join tn_field tf on tf.id = tfd.id
where tf.owner = rkkbase.id and tf.owner_type = rkkbase.id_type and tf.cmjfield = '{storage-field name}'

Время

select tfs."value" as v
from tn_field_timeonly tft
join tn_field tf on tf.id = tft.id
where tf.owner = rkkbase.id and tf.owner_type = rkkbase.id_type and tf.cmjfield = '{storage-field name}'

Дата и время

select tfs."value" as v
from tn_field_datetime tfdt
join tn_field tf on tf.id = tfdt.id
where tf.owner = rkkbase.id and tf.owner_type = rkkbase.id_type and tf.cmjfield = '{storage-field name}'

Субъект СО

coalesce((select string_agg(SPLIT_PART(tfs.value, '%', 1),',') as v
 			from tn_field_string tfs
 			join tn_field tf on tf.id = tfs.id
 			where tf.owner = rkkbase.id and tf.owner_type = rkkbase.id_type 
			and tf.cmjfield = '{storage-field name}'),'Не указано')

Атрибут комплексного справочника

Все атрибуты комлескного справочника текстовые.

Значения

SQL - запрос

Текст

(select title from classifier_complex where classifier_complex.id = (
      					select complex1.id
						from clscom_tag tag1
						left join clscom_to_tag to_tag1 on to_tag1.tag = tag1.id
						left join classifier_complex complex1 on complex1.id = to_tag1.classifier
						left join cls_typeComp typecom1 on typecom1.id = complex1.owner
						where tag1.schema = 'ComplectClassifierWP'
						and typecom1.alias = '{storage-field name}' and tag1.id = tag.id)) as Office

Сопоставление платформенного id с доменным id

Используется только для анализа

Значения

SQL - запрос

Текст

select doti."name", rkk.id, rkk.id_type, rkk.regnumprist,rkk.regnumcnt, rkk.regnumfin, convertid.sochiunid, convertid.notesunid from f_dp_rkk rkk 
join (select fdr.id as id, fdr.id_type as id_type, to_char(cast((fdr.id_type * 10^12) as bigint) + fdr.id, 'FM0000000000000000') as sochiunid
from f_dp_rkkbase fdr) rkkbase on rkk.id = rkkbase.id and rkk.id_type = rkkbase.id_type
join (select SUBSTRING(npm.punid FOR 16) as sochiunid, npm.nunid as notesunid
from nunid2punid_map npm) convertid on rkkbase.sochiunid=convertid.sochiunid
join domain_object_type_id doti on doti.id  = rkk.id_type

{storage-field name} - название св-ва схемы