Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- MERGE INTO appeals apl
- USING (
- -- все обращения, по которым необходимо проставить FID-ы
- WITH sq_appeals AS (
- SELECT
- id_appeal
- , fid_appeal
- , fid_type
- FROM
- appeals
- WHERE
- fid_post_data_status = -1
- AND fid_type IN (1, 2, 4, 7)
- )
- -- обращения клиентов
- , sq_client_appeals AS (
- SELECT
- ca.ext_id
- , ca.ops_index AS ops_index
- , apl.fid_type
- , ca.ufps_name AS ufps_name
- , apl.id_appeal
- FROM
- sq_appeals apl
- JOIN client_appeals ca
- ON apl.fid_appeal = ca.ext_id
- AND apl.fid_type = 1
- )
- -- обращения сотрудников
- , sq_staff_appeals AS (
- SELECT
- sa.ext_id
- , sa.ops AS ops_index
- , apl.fid_type
- , sa.ufps_title AS ufps_name
- , apl.id_appeal
- FROM
- sq_appeals apl
- JOIN staff_appeals sa
- ON apl.fid_appeal = sa.ext_id
- AND apl.fid_type = 2
- )
- -- обращения по доступной среде
- , sq_av_env_appeals AS (
- SELECT
- aa.ext_id
- , aa.post_index AS ops_index
- , apl.fid_type
- , NULL AS ufps_name
- , apl.id_appeal
- FROM
- sq_appeals apl
- JOIN available_env_appeals aa
- ON apl.fid_appeal = aa.ext_id
- AND apl.fid_type = 4
- )
- -- обращения по типу пенсии
- , sq_pension_appeals AS (
- SELECT
- pa.ext_id
- , pa.ops_index AS ops_index
- , apl.fid_type
- , NULL AS ufps_name
- , apl.id_appeal
- FROM
- sq_appeals apl
- JOIN pension_appeals pa
- ON apl.fid_appeal = pa.ext_id
- AND apl.fid_type = 7
- )
- -- все обращения, по которым необходимо проставить FID-ы
- , sq_all_appeals AS (
- SELECT * FROM sq_client_appeals
- UNION
- SELECT * FROM sq_staff_appeals
- UNION
- SELECT * FROM sq_av_env_appeals
- UNION
- SELECT * FROM sq_pension_appeals
- )
- --формируем справочник МР-УФПС
- , sq_ufps_mr as (
- SELECT DISTINCT FID_MR, FID_UFPS
- FROM MV_REL_MR_UFPS_POST
- )
- SELECT
- aa.id_appeal
- , CASE
- WHEN po.post_index IS NOT NULL -- нашлась связь в справочнике D_AVITO_POST_OFFICES
- THEN 0
- WHEN (aa.ops_index IS NOT NULL) AND (REGEXP_LIKE(aa.ops_index, '\d{6}')) -- индекс из обращения не найден в справочнике D_AVITO_POST_OFFICES,
- THEN 1 -- либо в поле индекса записан не индекс
- WHEN du.id_ufps IS NOT NULL -- индекса нет (либо он некорректный), но нашлось соответствие по УФПС в справочнике D_UFPS
- THEN 0
- WHEN aa.ufps_name IS NOT NULL -- УФПС не найден в справочнике D_UFPS
- THEN 2
- ELSE 3 -- не проставлен ни индекс, ни УФПС
- END AS fid_post_data_status
- , CASE WHEN po.post_index IS NOT NULL THEN po.fid_post_office END AS fid_post_office
- , CASE
- WHEN po.post_index IS NOT NULL THEN po.fid_ufps
- ELSE du.id_ufps
- END AS fid_ufps
- , CASE
- WHEN po.post_index IS NOT NULL THEN po.fid_mr
- ELSE rl.fid_mr
- END AS fid_mr
- FROM
- sq_all_appeals aa
- LEFT JOIN d_avito_post_offices po
- ON TRIM(aa.ops_index) = TRIM(po.post_index)
- LEFT JOIN d_ufps du
- ON UPPER(TRIM(aa.ufps_name)) = UPPER(TRIM(du.ufps_name))
- LEFT JOIN sq_ufps_mr rl
- ON du.id_ufps = rl.fid_ufps
- ) sq
- ON (apl.id_appeal = sq.id_appeal)
- WHEN MATCHED THEN
- UPDATE SET
- apl.fid_mr = sq.fid_mr
- , apl.fid_ufps = sq.fid_ufps
- , apl.fid_post_office = sq.fid_post_office
- , apl.fid_post_data_status = sq.fid_post_data_status
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement