ERROR,""DXL-to-PlStmt Translation: Attribute number 72 not found in project list

[adnanhamdussalam]

Hi Team,

GPORA getting is failed while sharing the title error during executing an SP having a table more than 72 columns.
It is resulting in generating to many spill files and causing slowness:

CB Version: postgres (Apache Cloudberry) 2.1.0-incubating build bdf90c5
Please can you confirm as it seems like a bug?

[liang8283]

Hi @adnanhamdussalam ,

Could you please share the related DDLs and the SP so that we can try to reproduce the issue? Thanks.

[adnanhamdussalam]

Hi @liang8283,

PFB the required information:

CREATE OR REPLACE FUNCTION crm_lookup()
RETURNS void
LANGUAGE plpgsql
VOLATILE
AS $$

begin

-- pick records where atleast 1 relaz = row_id_asset
drop table if exists applied_ai_staging.crm_relaz;
create table applied_ai_staging.crm_relaz
WITH (
appendonly=true,
compresstype=zstd
) as
select
RELAZ_ABILITANTE_ABILITATO, sum(case when RELAZ_ABILITANTE_ABILITATO = ROW_ID_ASSET then 1 else 0 end) IS_SAME
from
customer_base
where
RELAZ_ABILITANTE_ABILITATO is not null
group by
1; -- ~38s

--

drop table if exists crm_distinct_relaz;
create table crm_distinct_relaz
WITH (
appendonly=true,
compresstype=zstd
) as
select
*
from
crm_relaz
where
IS_SAME < 1; -- 2s

-- create new column, has values as original column for now

drop table if exists crm_crm_new__;
create table crm_crm_new__
WITH (
appendonly=true,
compresstype=zstd
) as
select
*, STATO_COMMERCIALE as STATO_COMMERCIALE_NEW
from
customer_base; -- 4m

-- update new column to cessato for relaz which don't have a signle corresponding row_id_asset

update
crm_crm_new__
set
STATO_COMMERCIALE_NEW = 'CESSATO'
where
RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from crm_distinct_relaz where RELAZ_ABILITANTE_ABILITATO is not null); -- 3m

---

-- step 1: population intestatario

---

-- create ref x table with original logic -> this would also be used in step 5 of btn population

drop table if exists ref_x;
create table ref_x
WITH (
appendonly=true,
compresstype=zstd
) as
select distinct
RELAZ_ABILITANTE_ABILITATO
from
crm_crm_new__
where
RELAZ_ABILITANTE_ABILITATO = ROW_ID_ASSET and RELAZ_ABILITANTE_ABILITATO is not null; -- 1m having count(distinct case when tiid is pop then tiid end) = 1

-- relaz and asset same

drop table if exists crm_relaz_asset_same;
create table crm_relaz_asset_same
WITH (
appendonly=true,
compresstype=zstd
) as
select distinct
RELAZ_ABILITANTE_ABILITATO, TI_ID_INTESTATARIO
from
crm_crm_new__
where
(RELAZ_ABILITANTE_ABILITATO = ROW_ID_ASSET
and TI_ID_INTESTATARIO != '' and TI_ID_INTESTATARIO is not null and TI_ID_INTESTATARIO != 'NULL' and TI_ID_INTESTATARIO != 'none' and TI_ID_INTESTATARIO != 'ND')
and END_DATE >= '2050-01-01'
group by
1, 2; -- 30s

drop table if exists crm_relaz_asset_same_copy;
create table crm_relaz_asset_same_copy
WITH (
appendonly=true,
compresstype=zstd
) as -- making a copy table for next insert
select
*
from
crm_relaz_asset_same; -- 2s

insert into crm_relaz_asset_same -- > case when ref_z holds true but tiid is not popualted against record where relaz = row_id_asset
select distinct
RELAZ_ABILITANTE_ABILITATO, TI_ID_INTESTATARIO
from
crm_crm_new__
where
RELAZ_ABILITANTE_ABILITATO not in (select distinct RELAZ_ABILITANTE_ABILITATO from crm_relaz_asset_same_copy where RELAZ_ABILITANTE_ABILITATO is not null)
and RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from ref_x where RELAZ_ABILITANTE_ABILITATO is not null)
and TI_ID_INTESTATARIO != '' and TI_ID_INTESTATARIO is not null and TI_ID_INTESTATARIO != 'NULL' and TI_ID_INTESTATARIO != 'none' and TI_ID_INTESTATARIO != 'ND'; -- 2m

-- relaz asset diff

drop table if exists crm_relaz_asset_diff;
create table crm_relaz_asset_diff
WITH (
appendonly=true,
compresstype=zstd
) as -- relaz not present in previous table, dont have any row_id_asset equal and are populated
select distinct
RELAZ_ABILITANTE_ABILITATO, max(TI_ID_INTESTATARIO) as TI_ID_INTESTATARIO
from
crm_crm_new__
where
RELAZ_ABILITANTE_ABILITATO not in (select distinct RELAZ_ABILITANTE_ABILITATO from ref_x where RELAZ_ABILITANTE_ABILITATO is not null)
and TI_ID_INTESTATARIO != '' and TI_ID_INTESTATARIO is not null and TI_ID_INTESTATARIO != 'NULL' and TI_ID_INTESTATARIO != 'none' and TI_ID_INTESTATARIO != 'ND'
and END_DATE >= '2050-01-01'
group by
1; -- 2m

-- create tiid_new column

drop table if exists crm_crm_new_;
create table crm_crm_new_
WITH (
appendonly=true,
compresstype=zstd
) as
select
*, TI_ID_INTESTATARIO as TI_ID_INTESTATARIO_NEW
from
crm_crm_new__; -- 3m

-- update new tiid column with relaz = row_id_asset table
set optimizer = 'OFF';

update
crm_crm_new_
set
TI_ID_INTESTATARIO_NEW = crm_relaz_asset_same.TI_ID_INTESTATARIO
from
crm_relaz_asset_same
where
crm_crm_new_.RELAZ_ABILITANTE_ABILITATO = crm_relaz_asset_same.RELAZ_ABILITANTE_ABILITATO
and (TI_ID_INTESTATARIO_NEW = '' or TI_ID_INTESTATARIO_NEW is null or TI_ID_INTESTATARIO_NEW = 'NULL' or TI_ID_INTESTATARIO_NEW = 'none' or TI_ID_INTESTATARIO_NEW = 'ND'); -- 5m

-- update new tiid column where relaz != row_id_asset table

update
crm_crm_new_
set
TI_ID_INTESTATARIO_NEW = crm_relaz_asset_diff.TI_ID_INTESTATARIO
from
crm_relaz_asset_diff
where
crm_crm_new_.RELAZ_ABILITANTE_ABILITATO = crm_relaz_asset_diff.RELAZ_ABILITANTE_ABILITATO
and (TI_ID_INTESTATARIO_NEW = '' or TI_ID_INTESTATARIO_NEW is null or TI_ID_INTESTATARIO_NEW = 'NULL' or TI_ID_INTESTATARIO_NEW = 'none' or TI_ID_INTESTATARIO_NEW = 'ND'); -- 1m

set optimizer = 'ON';

---

-- step 2: create mapping

---

drop table if exists crm_crm_mapping_;
create table crm_crm_mapping_ as
select distinct
TI_ID_INTESTATARIO_NEW,
1 IS_MAIN_NUMBER,
max(case when AMBITO in ('Mobile', 'Mobile') then 1 else 0 end) IS_Mobile,
max(case when AMBITO in ('Fisso', 'Fisso') then 1 else 0 end) IS_FIXED,
NUMERO_TELEFONO,
max(case when STATO_COMMERCIALE_NEW = 'ATTIVO' -- and RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from ba_dev.crm_distinct_relaz where RELAZ_ABILITANTE_ABILITATO is not null)
then 1 else 0 end) IS_ACTIVE
from
crm_crm_new_
where
RELAZ_ABILITANTE_ABILITATO = ROW_ID_ASSET and PRODOTTO_CLASS = 'SERVIZIO_ABILITANTE' and NUMERO_TELEFONO != '' and NUMERO_TELEFONO is not null and NUMERO_TELEFONO != 'NULL'
and TI_ID_INTESTATARIO_NEW != '' and TI_ID_INTESTATARIO_NEW is not null and TI_ID_INTESTATARIO_NEW != 'NULL'
group by
TI_ID_INTESTATARIO_NEW, NUMERO_TELEFONO
distributed by (NUMERO_TELEFONO, TI_ID_INTESTATARIO_NEW); -- 2m

ALTER TABLE crm_crm_mapping_ ADD PRIMARY KEY (NUMERO_TELEFONO, TI_ID_INTESTATARIO_NEW);

-- prodotto class = elemento

INSERT INTO
crm_crm_mapping_
SELECT
crm_crm_new_.TI_ID_INTESTATARIO_NEW,
0 IS_MAIN_NUMBER,
max(case when AMBITO in ('Mobile', 'Mobile') then 1 else 0 end) IS_Mobile,
max(case when AMBITO in ('Fisso', 'Fisso') then 1 else 0 end) IS_FIXED,
crm_crm_new_.NUMERO_TELEFONO,
max(case when STATO_COMMERCIALE_NEW = 'ATTIVO' -- and RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from ba_dev.crm_distinct_relaz where RELAZ_ABILITANTE_ABILITATO is not null)
then 1 else 0 end) IS_ACTIVE
from
crm_crm_new_
LEFT JOIN
crm_crm_mapping_
ON
crm_crm_new_.TI_ID_INTESTATARIO_NEW = crm_crm_mapping_.TI_ID_INTESTATARIO_NEW
and
crm_crm_new_.NUMERO_TELEFONO = crm_crm_mapping_.NUMERO_TELEFONO
WHERE
crm_crm_mapping_.TI_ID_INTESTATARIO_NEW IS null
and
(crm_crm_new_.NUMERO_TELEFONO != '' and crm_crm_new_.NUMERO_TELEFONO is not null and crm_crm_new_.NUMERO_TELEFONO != 'NULL'
and crm_crm_new_.TI_ID_INTESTATARIO_NEW != '' and crm_crm_new_.TI_ID_INTESTATARIO_NEW is not null and crm_crm_new_.TI_ID_INTESTATARIO_NEW != 'NULL')
group by
1, 5; -- 2m

-- adding associated ID

drop table if exists crm_crm_mapping_copy;
create table crm_crm_mapping_copy
WITH (
appendonly=true,
compresstype=zstd
) as
select
TI_ID_INTESTATARIO_NEW,
max(IS_MAIN_NUMBER) IS_MAIN_NUMBER,
max(IS_Mobile) IS_Mobile,
max(IS_FIXED) IS_FIXED,
max(NUMERO_TELEFONO) NUMERO_TELEFONO,
max(IS_ACTIVE) IS_ACTIVE
from
crm_crm_mapping_
where
IS_MAIN_NUMBER = 1
group by
TI_ID_INTESTATARIO_NEW; -- 13s

--

drop table if exists crm_crm_mapping;
create table crm_crm_mapping as
select distinct
a.*,
(case when a.NUMERO_TELEFONO = b.NUMERO_TELEFONO and a.TI_ID_INTESTATARIO_NEW != b.TI_ID_INTESTATARIO_NEW and a.IS_MAIN_NUMBER = 0 then b.TI_ID_INTESTATARIO_NEW else 'NA' end) ASSOCIATED_ID
from
crm_crm_mapping_ as a left join crm_crm_mapping_copy as b
on
a.NUMERO_TELEFONO = b.NUMERO_TELEFONO and a.TI_ID_INTESTATARIO_NEW != b.TI_ID_INTESTATARIO_NEW
distributed by (NUMERO_TELEFONO, TI_ID_INTESTATARIO_NEW); -- 2m

ALTER TABLE crm_crm_mapping ADD PRIMARY KEY (NUMERO_TELEFONO, TI_ID_INTESTATARIO_NEW, ASSOCIATED_ID);

---

-- Step 3 num telefono column in crm

---

drop table if exists ref_x_new;
create table ref_x_new
WITH (
appendonly=true,
compresstype=zstd
) as
select distinct
RELAZ_ABILITANTE_ABILITATO, count(distinct case when TI_ID_INTESTATARIO_NEW != '' and TI_ID_INTESTATARIO_NEW is not null then TI_ID_INTESTATARIO_NEW end) TI_ID_INTESTATARIO_NEW
from
crm_crm_new_
where
RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from ref_x where RELAZ_ABILITANTE_ABILITATO is not null) and RELAZ_ABILITANTE_ABILITATO is not null
group by
RELAZ_ABILITANTE_ABILITATO
having
count(distinct case when TI_ID_INTESTATARIO_NEW != '' and TI_ID_INTESTATARIO_NEW is not null then TI_ID_INTESTATARIO_NEW end) = 1; -- 1m having count(distinct case when tiid is pop then tiid end) = 1

drop table if exists crm_crm_btn_Mobile;
create table crm_crm_btn_Mobile
WITH (
appendonly=true,
compresstype=zstd
) as
select
RELAZ_ABILITANTE_ABILITATO, AMBITO, max(NUMERO_TELEFONO) as NUMERO_TELEFONO_MAIN_Mobile
from
crm_crm_new_
where
RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from ref_x_new where RELAZ_ABILITANTE_ABILITATO is not null)
and PRODOTTO_CLASS = 'SERVIZIO_ABILITANTE' and AMBITO in ('Mobile', 'Fisso') and NUMERO_TELEFONO != '' and NUMERO_TELEFONO is not null and NUMERO_TELEFONO != 'NULL'
group by
1, 2; -- 25s

--

drop table if exists crm_crm_new;
create table crm_crm_new
WITH (
appendonly=true,
compresstype=zstd
) as
select
*, NUMERO_TELEFONO as NUMERO_TELEFONO_MAIN
from
crm_crm_new_; -- 4 mins

update
crm_crm_new
set
NUMERO_TELEFONO_MAIN = crm_crm_btn_Mobile.NUMERO_TELEFONO_MAIN_Mobile
from
crm_crm_btn_Mobile
where
crm_crm_new.RELAZ_ABILITANTE_ABILITATO = crm_crm_btn_Mobile.RELAZ_ABILITANTE_ABILITATO
and crm_crm_new.AMBITO = crm_crm_btn_Mobile.AMBITO
and (crm_crm_new.NUMERO_TELEFONO_MAIN is null or crm_crm_new.NUMERO_TELEFONO_MAIN = '' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'NULL' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'none' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'ND'); --5m

-- pick relaz and tiid against which there are no btn

drop table if exists crm_relaz_no_btn;
create table crm_relaz_no_btn
WITH (
appendonly=true,
compresstype=zstd
) as
select
RELAZ_ABILITANTE_ABILITATO, TI_ID_INTESTATARIO_NEW -- sum(case when NUMERO_TELEFONO_MAIN is not null then 1 else 0 end) IS_SAME
from
crm_crm_new
where
RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from ref_x_new where RELAZ_ABILITANTE_ABILITATO is not null) -- ref x holds true
and
RELAZ_ABILITANTE_ABILITATO is not null
and
TI_ID_INTESTATARIO_NEW != '' and TI_ID_INTESTATARIO_NEW is not null and TI_ID_INTESTATARIO_NEW != 'NULL' and TI_ID_INTESTATARIO_NEW != 'none' and TI_ID_INTESTATARIO_NEW != 'ND'
group by
1, 2
having
sum(case when NUMERO_TELEFONO_MAIN is not null and NUMERO_TELEFONO_MAIN != '' then 1 else 0 end) = 0; -- 45s

--

drop table if exists crm_crm_mapping_ambito;
create table crm_crm_mapping_ambito
WITH (
appendonly=true,
compresstype=zstd
) as
select
nullif(TI_ID_INTESTATARIO_NEW, '') TI_ID_INTESTATARIO_NEW, IS_MAIN_NUMBER, IS_Mobile, IS_FIXED, NUMERO_TELEFONO, IS_ACTIVE, ASSOCIATED_ID,
(case when IS_Mobile = 1 then 'Mobile' when IS_FIXED = 1 then 'Fisso' end) as AMBITO_MAPPING
from
crm_crm_mapping; -- 3s

--

drop table if exists crm_crm_mapping_ambito_no_btn;
create table crm_crm_mapping_ambito_no_btn
WITH (
appendonly=true,
compresstype=zstd
) as
select
nullif(TI_ID_INTESTATARIO_NEW, '') TI_ID_INTESTATARIO_NEW,
max(IS_MAIN_NUMBER) IS_MAIN_NUMBER,
max(IS_Mobile) IS_Mobile,
max(IS_FIXED) IS_FIXED,
max(NUMERO_TELEFONO) NUMERO_TELEFONO,
max(IS_ACTIVE) IS_ACTIVE,
max(ASSOCIATED_ID) ASSOCIATED_ID,
max(AMBITO_MAPPING) AMBITO_MAPPING
from
crm_crm_mapping_ambito
where
TI_ID_INTESTATARIO_NEW in (select distinct TI_ID_INTESTATARIO_NEW from crm_relaz_no_btn where TI_ID_INTESTATARIO_NEW is not null)
-- below condition and max conditions in select added by zaryab -> confirm with AI
and IS_ACTIVE = 1 and IS_Mobile = 1 and IS_FIXED = 0-- this would ignore all inactive records => need to figure how to give priority to active records and in case none are present, pick inactive
group by
1; -- 4s

insert into
crm_crm_mapping_ambito_no_btn
select
nullif(TI_ID_INTESTATARIO_NEW, '') TI_ID_INTESTATARIO_NEW,
max(IS_MAIN_NUMBER),
max(IS_Mobile),
max(IS_FIXED),
max(NUMERO_TELEFONO),
max(IS_ACTIVE),
max(ASSOCIATED_ID),
max(AMBITO_MAPPING)
from
crm_crm_mapping_ambito
where
TI_ID_INTESTATARIO_NEW in (select distinct TI_ID_INTESTATARIO_NEW from crm_relaz_no_btn where TI_ID_INTESTATARIO_NEW is not null)
-- below condition and max conditions in select added by zaryab -> confirm with AI
and IS_ACTIVE = 1 and IS_FIXED = 1 and IS_Mobile = 0-- this would ignore all inactive records => need to figure how to give priority to active records and in case none are present, pick inactive
group by
1;

drop table if exists crm_crm_mapping_ambito_no_btn_not_active;
create table crm_crm_mapping_ambito_no_btn_not_active
WITH (
appendonly=true,
compresstype=zstd
) as
select
nullif(TI_ID_INTESTATARIO_NEW, '') as TI_ID_INTESTATARIO_NEW,
max(IS_MAIN_NUMBER) as IS_MAIN_NUMBER,
max(IS_Mobile) as IS_Mobile,
max(IS_FIXED) as IS_FIXED,
max(NUMERO_TELEFONO) as NUMERO_TELEFONO,
max(IS_ACTIVE) as IS_ACTIVE,
max(ASSOCIATED_ID) as ASSOCIATED_ID,
max(AMBITO_MAPPING) as AMBITO_MAPPING
from
crm_crm_mapping_ambito
where
TI_ID_INTESTATARIO_NEW in (select distinct TI_ID_INTESTATARIO_NEW from crm_relaz_no_btn where TI_ID_INTESTATARIO_NEW is not null)
-- below condition and max conditions in select added by zaryab -> confirm with AI
and IS_ACTIVE = 0 and IS_FIXED = 1 and IS_Mobile = 0 -- this would ignore all inactive records => need to figure how to give priority to active records and in case none are present, pick inactive
group by
1;

insert into
crm_crm_mapping_ambito_no_btn_not_active
select
nullif(TI_ID_INTESTATARIO_NEW, '') as TI_ID_INTESTATARIO_NEW,
max(IS_MAIN_NUMBER) as IS_MAIN_NUMBER,
max(IS_Mobile) as IS_Mobile,
max(IS_FIXED) as IS_FIXED,
max(NUMERO_TELEFONO) as NUMERO_TELEFONO,
max(IS_ACTIVE) as IS_ACTIVE,
max(ASSOCIATED_ID) as ASSOCIATED_ID,
max(AMBITO_MAPPING) as AMBITO_MAPPING
from
crm_crm_mapping_ambito
where
TI_ID_INTESTATARIO_NEW in (select distinct TI_ID_INTESTATARIO_NEW from crm_relaz_no_btn where TI_ID_INTESTATARIO_NEW is not null)
-- below condition and max conditions in select added by zaryab -> confirm with AI
and IS_ACTIVE = 0 and IS_FIXED = 0 and IS_Mobile = 1 -- this would ignore all inactive records => need to figure how to give priority to active records and in case none are present, pick inactive
group by
1;

update
crm_crm_new
set
NUMERO_TELEFONO_MAIN = crm_crm_mapping_ambito_no_btn.NUMERO_TELEFONO
from
crm_crm_mapping_ambito_no_btn
where
crm_crm_new.TI_ID_INTESTATARIO_NEW = crm_crm_mapping_ambito_no_btn.TI_ID_INTESTATARIO_NEW
and crm_crm_new.AMBITO = crm_crm_mapping_ambito_no_btn.AMBITO_MAPPING
and crm_crm_mapping_ambito_no_btn.IS_MAIN_NUMBER = 1
and (crm_crm_new.NUMERO_TELEFONO_MAIN is null or crm_crm_new.NUMERO_TELEFONO_MAIN = '' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'NULL' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'none' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'ND')
and crm_crm_new.RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from crm_relaz_no_btn where RELAZ_ABILITANTE_ABILITATO is not null); -- 2m

update
crm_crm_new
set
NUMERO_TELEFONO_MAIN = crm_crm_mapping_ambito_no_btn_not_active.NUMERO_TELEFONO
from
crm_crm_mapping_ambito_no_btn_not_active
where
crm_crm_new.TI_ID_INTESTATARIO_NEW = crm_crm_mapping_ambito_no_btn_not_active.TI_ID_INTESTATARIO_NEW
and crm_crm_new.AMBITO = crm_crm_mapping_ambito_no_btn_not_active.AMBITO_MAPPING
and crm_crm_mapping_ambito_no_btn_not_active.IS_MAIN_NUMBER = 1
and (crm_crm_new.NUMERO_TELEFONO_MAIN is null or crm_crm_new.NUMERO_TELEFONO_MAIN = '' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'NULL' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'none' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'ND')
and crm_crm_new.RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from crm_relaz_no_btn where RELAZ_ABILITANTE_ABILITATO is not null);

drop table if exists crm_crm_mapping_ambito_no_btn_only_tiid;
create table crm_crm_mapping_ambito_no_btn_only_tiid
WITH (
appendonly=true,
compresstype=zstd
) as
select
nullif(TI_ID_INTESTATARIO_NEW, '') TI_ID_INTESTATARIO_NEW,
max(IS_MAIN_NUMBER) IS_MAIN_NUMBER,
max(IS_Mobile) IS_Mobile,
max(IS_FIXED) IS_FIXED,
max(NUMERO_TELEFONO) NUMERO_TELEFONO,
max(IS_ACTIVE) IS_ACTIVE,
max(ASSOCIATED_ID) ASSOCIATED_ID,
max(AMBITO_MAPPING) AMBITO_MAPPING
from
crm_crm_mapping_ambito_no_btn
where
TI_ID_INTESTATARIO_NEW in (select distinct TI_ID_INTESTATARIO_NEW from crm_relaz_no_btn where TI_ID_INTESTATARIO_NEW is not null)
-- below condition and max conditions in select added by zaryab -> confirm with AI
-- and IS_ACTIVE = 1 and IS_FIXED = 1 and IS_Mobile = 0-- this would ignore all inactive records => need to figure how to give priority to active records and in case none are present, pick inactive
group by
1;

drop table if exists crm_crm_mapping_ambito_no_btn_not_active_only_tiid;
create table crm_crm_mapping_ambito_no_btn_not_active_only_tiid
WITH (
appendonly=true,
compresstype=zstd
) as
select
nullif(TI_ID_INTESTATARIO_NEW, '') as TI_ID_INTESTATARIO_NEW,
max(IS_MAIN_NUMBER) as IS_MAIN_NUMBER,
max(IS_Mobile) as IS_Mobile,
max(IS_FIXED) as IS_FIXED,
max(NUMERO_TELEFONO) as NUMERO_TELEFONO,
max(IS_ACTIVE) as IS_ACTIVE,
max(ASSOCIATED_ID) as ASSOCIATED_ID,
max(AMBITO_MAPPING) as AMBITO_MAPPING
from
crm_crm_mapping_ambito_no_btn_not_active
where
TI_ID_INTESTATARIO_NEW in (select distinct TI_ID_INTESTATARIO_NEW from crm_relaz_no_btn where TI_ID_INTESTATARIO_NEW is not null)
-- below condition and max conditions in select added by zaryab -> confirm with AI
-- and IS_ACTIVE = 0 and IS_FIXED = 1 and IS_Mobile = 0 -- this would ignore all inactive records => need to figure how to give priority to active records and in case none are present, pick inactive
group by
1;

update
crm_crm_new
set
NUMERO_TELEFONO_MAIN = crm_crm_mapping_ambito_no_btn_only_tiid.NUMERO_TELEFONO
from
crm_crm_mapping_ambito_no_btn_only_tiid
where
crm_crm_new.TI_ID_INTESTATARIO_NEW = crm_crm_mapping_ambito_no_btn_only_tiid.TI_ID_INTESTATARIO_NEW
and (crm_crm_new.NUMERO_TELEFONO_MAIN is null or crm_crm_new.NUMERO_TELEFONO_MAIN = '' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'NULL' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'none' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'ND')
and crm_crm_new.RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from crm_relaz_no_btn where RELAZ_ABILITANTE_ABILITATO is not null); -- 2m

update
crm_crm_new
set
NUMERO_TELEFONO_MAIN = crm_crm_mapping_ambito_no_btn_not_active_only_tiid.NUMERO_TELEFONO
from
crm_crm_mapping_ambito_no_btn_not_active_only_tiid
where
crm_crm_new.TI_ID_INTESTATARIO_NEW = crm_crm_mapping_ambito_no_btn_not_active_only_tiid.TI_ID_INTESTATARIO_NEW
and (crm_crm_new.NUMERO_TELEFONO_MAIN is null or crm_crm_new.NUMERO_TELEFONO_MAIN = '' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'NULL' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'none' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'ND')
and crm_crm_new.RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from crm_relaz_no_btn where RELAZ_ABILITANTE_ABILITATO is not null);

--

drop table if exists crm_relaz_same_tiid;
create table crm_relaz_same_tiid
WITH (
appendonly=true,
compresstype=zstd
) as
select
RELAZ_ABILITANTE_ABILITATO, count(distinct TI_ID_INTESTATARIO_NEW) TI_ID_INTESTATARIO_NEW
from
crm_crm_new
where
RELAZ_ABILITANTE_ABILITATO is not null
and
TI_ID_INTESTATARIO_NEW != '' and TI_ID_INTESTATARIO_NEW is not null and TI_ID_INTESTATARIO_NEW != 'NULL' and TI_ID_INTESTATARIO_NEW != 'none' and TI_ID_INTESTATARIO_NEW != 'ND'
group by
1
having
count(distinct TI_ID_INTESTATARIO_NEW) = 1; -- 40s

--

drop table if exists crm_relaz_elemento;
create table crm_relaz_elemento
WITH (
appendonly=true,
compresstype=zstd
) as
select distinct
RELAZ_ABILITANTE_ABILITATO,
max(AMBITO) AMBITO,
max(NUMERO_TELEFONO_MAIN) as NUMERO_TELEFONO_MAIN_Fisso
from
crm_crm_new
where
RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from ref_x_new where RELAZ_ABILITANTE_ABILITATO is not null) -- ref x holds true
and PRODOTTO_CLASS = 'ELEMENTO_ABILITATO' and AMBITO in ('Fisso', 'Mobile') and NUMERO_TELEFONO_MAIN != '' and NUMERO_TELEFONO_MAIN is not null and NUMERO_TELEFONO_MAIN != 'NULL'
and RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from crm_relaz_same_tiid where RELAZ_ABILITANTE_ABILITATO is not null)
-- group by added by zaryab along with above max statements, confirm from AI
group by
RELAZ_ABILITANTE_ABILITATO; -- 1m

update
crm_crm_new
set
NUMERO_TELEFONO_MAIN = crm_relaz_elemento.NUMERO_TELEFONO_MAIN_Fisso
from
crm_relaz_elemento
where
crm_crm_new.RELAZ_ABILITANTE_ABILITATO = crm_relaz_elemento.RELAZ_ABILITANTE_ABILITATO
and crm_crm_new.AMBITO = crm_relaz_elemento.AMBITO
and (crm_crm_new.NUMERO_TELEFONO_MAIN is null or crm_crm_new.NUMERO_TELEFONO_MAIN = '' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'NULL' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'none' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'ND')
and crm_crm_new.RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from crm_relaz_same_tiid where RELAZ_ABILITANTE_ABILITATO is not null); -- 2m

-- step 5

drop table if exists crm_relaz_multiple_tiid;
create table crm_relaz_multiple_tiid
WITH (
appendonly=true,
compresstype=zstd
) as
select
RELAZ_ABILITANTE_ABILITATO, count(distinct TI_ID_INTESTATARIO_NEW) TI_ID_INTESTATARIO_NEW
from
crm_crm_new
where
RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from ref_x where RELAZ_ABILITANTE_ABILITATO is not null) -- ref x holds true
and
RELAZ_ABILITANTE_ABILITATO is not null
group by
1
having
count(distinct TI_ID_INTESTATARIO_NEW) > 1; -- 1m

--

drop table if exists crm_relaz_multiple;
create table crm_relaz_multiple
WITH (
appendonly=true,
compresstype=zstd
) as
select distinct
RELAZ_ABILITANTE_ABILITATO,
max(TI_ID_INTESTATARIO_NEW) TI_ID_INTESTATARIO_NEW,
max(AMBITO) AMBITO,
max(NUMERO_TELEFONO_MAIN) as NUMERO_TELEFONO_MAIN_Fisso
from
crm_crm_new
where
RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from ref_x where RELAZ_ABILITANTE_ABILITATO is not null) -- ref x holds true
and RELAZ_ABILITANTE_ABILITATO = ROW_ID_ASSET
and AMBITO in ('Fisso') and NUMERO_TELEFONO != '' and NUMERO_TELEFONO is not null and NUMERO_TELEFONO != 'NULL'
and RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from crm_relaz_multiple_tiid where RELAZ_ABILITANTE_ABILITATO is not null)
group by -- group by and max added by zaryab
1; -- 35s

insert into
crm_relaz_multiple
select distinct
RELAZ_ABILITANTE_ABILITATO,
max(TI_ID_INTESTATARIO_NEW),
max(AMBITO),
max(NUMERO_TELEFONO_MAIN) as NUMERO_TELEFONO_MAIN_Fisso
from
crm_crm_new
where
RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from ref_x where RELAZ_ABILITANTE_ABILITATO is not null) -- ref x holds true
and RELAZ_ABILITANTE_ABILITATO = ROW_ID_ASSET
and AMBITO in ('Mobile') and NUMERO_TELEFONO != '' and NUMERO_TELEFONO is not null and NUMERO_TELEFONO != 'NULL'
and RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from crm_relaz_multiple_tiid where RELAZ_ABILITANTE_ABILITATO is not null)
group by -- group by and max added by zaryab
1;

update
crm_crm_new
set
NUMERO_TELEFONO_MAIN = crm_relaz_multiple.NUMERO_TELEFONO_MAIN_Fisso
from
crm_relaz_multiple
where
crm_crm_new.RELAZ_ABILITANTE_ABILITATO = crm_relaz_multiple.RELAZ_ABILITANTE_ABILITATO
and crm_crm_new.TI_ID_INTESTATARIO_NEW = crm_relaz_multiple.TI_ID_INTESTATARIO_NEW
and crm_crm_new.AMBITO = crm_relaz_multiple.AMBITO
and (crm_crm_new.NUMERO_TELEFONO_MAIN is null or crm_crm_new.NUMERO_TELEFONO_MAIN = '' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'NULL' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'none' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'ND')
and crm_crm_new.RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from crm_relaz_multiple_tiid where RELAZ_ABILITANTE_ABILITATO is not null); --2m

--

drop table if exists crm_tiid_not_in;
create table crm_tiid_not_in
WITH (
appendonly=true,
compresstype=zstd
) as
select distinct
TI_ID_INTESTATARIO_NEW
from
crm_crm_new
where
RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from ref_x where RELAZ_ABILITANTE_ABILITATO is not null) -- ref x holds true
and
TI_ID_INTESTATARIO_NEW not in (select distinct TI_ID_INTESTATARIO_NEW from crm_relaz_multiple where TI_ID_INTESTATARIO_NEW is not null); -- 1m

drop table if exists crm_tiid_from_mapping;
create table crm_tiid_from_mapping
WITH (
appendonly=true,
compresstype=zstd
) as
select
TI_ID_INTESTATARIO_NEW,
max('Mobile') AMBITO,
max(NUMERO_TELEFONO) as NUMERO_TELEFONO_MAPPING
from
crm_crm_mapping
where
TI_ID_INTESTATARIO_NEW in (select distinct TI_ID_INTESTATARIO_NEW from crm_tiid_not_in where TI_ID_INTESTATARIO_NEW is not null)
and IS_Mobile = 1
-- group by and max added by zaryab
group by
TI_ID_INTESTATARIO_NEW;

insert into
crm_tiid_from_mapping
select
TI_ID_INTESTATARIO_NEW,
max('Fisso') as AMBITO,
max(NUMERO_TELEFONO) as NUMERO_TELEFONO_MAPPING
from
crm_crm_mapping
where
TI_ID_INTESTATARIO_NEW in (select distinct TI_ID_INTESTATARIO_NEW from crm_tiid_not_in where TI_ID_INTESTATARIO_NEW is not null)
and IS_FIXED = 1
-- group by and max added by zaryab
group by
TI_ID_INTESTATARIO_NEW;

--

update
crm_crm_new
set
NUMERO_TELEFONO_MAIN = crm_tiid_from_mapping.NUMERO_TELEFONO_MAPPING
from
crm_tiid_from_mapping
where
crm_crm_new.TI_ID_INTESTATARIO_NEW = crm_tiid_from_mapping.TI_ID_INTESTATARIO_NEW
and crm_crm_new.AMBITO = crm_tiid_from_mapping.AMBITO
and (crm_crm_new.NUMERO_TELEFONO_MAIN is null or crm_crm_new.NUMERO_TELEFONO_MAIN = '' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'NULL' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'none' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'ND'); -- 4m

-- step 4

drop table if exists crm_crm_final;
create table crm_crm_final
WITH (
appendonly=true,
compresstype=zstd,
orientation = column
) as
select
a.*,
(case when a.TI_ID_INTESTATARIO_NEW = b.TI_ID_INTESTATARIO_NEW and a.AMBITO = (case when IS_Mobile = 1 then 'Mobile'end) then b.NUMERO_TELEFONO end) MAIN_Mobile_NUMBER,
(case when a.TI_ID_INTESTATARIO_NEW = b.TI_ID_INTESTATARIO_NEW and a.AMBITO = (case when IS_FIXED = 1 then 'Fisso'end) then b.NUMERO_TELEFONO end) MAIN_Fisso_NUMBER
from
crm_crm_new as a left join crm_crm_mapping_ambito as b
on
a.TI_ID_INTESTATARIO_NEW = b.TI_ID_INTESTATARIO_NEW and a.AMBITO = b.AMBITO_MAPPING and b.IS_MAIN_NUMBER = 1;

-- rollup btn ---ch

drop table if exists crm_crm2_scd_new_key1;
create table crm_crm2_scd_new_key1
WITH (
appendonly=true,
compresstype=zstd
) as
select
NUMERO_TELEFONO_MAIN as NUMERO_TELEFONO_MAIN_BTN,
max(TI_ID_INTESTATARIO_NEW) as TI_ID_INTESTATARIO_NEW_BTN,
-- group_concat(distinct CODICE_FISCALE order by CODICE_FISCALE) CODICE_FISCALE_BTN,
string_agg(distinct case when CODICE_FISCALE<>'' then CODICE_FISCALE end , ',') CODICE_FISCALE_BTN,
START_DATE,
LEAD(START_DATE,1) OVER (
partition by NUMERO_TELEFONO_MAIN
ORDER BY START_DATE
) END_DATE
from
crm_crm_final
group by
NUMERO_TELEFONO_MAIN, START_DATE; -- 2h,9m

delete from crm_crm2_scd_new_key1 where NUMERO_TELEFONO_MAIN_BTN = '';
update crm_crm2_scd_new_key1 set END_DATE = '2999-12-31' where END_DATE is null;
-- rollup TIID

drop table if exists crm_crm1_scd_new_key1;
create table crm_crm1_scd_new_key1
WITH (
appendonly=true,
compresstype=zstd
) as
select
TI_ID_INTESTATARIO_NEW as TI_ID_INTESTATARIO_NEW_TIID,
max(NUMERO_TELEFONO_MAIN) as NUMERO_TELEFONO_MAIN_TIID,
-- group_concat(distinct CODICE_FISCALE order by CODICE_FISCALE) CODICE_FISCALE_TIID,
string_agg(distinct case when CODICE_FISCALE<>'' then CODICE_FISCALE end , ',') CODICE_FISCALE_TIID,
START_DATE,
LEAD(START_DATE,1) OVER (
partition by TI_ID_INTESTATARIO_NEW
ORDER BY START_DATE
) END_DATE
from
crm_crm_final
group by
TI_ID_INTESTATARIO_NEW, START_DATE; --1h

--

delete from crm_crm1_scd_new_key1 where TI_ID_INTESTATARIO_NEW_TIID = '';
update crm_crm1_scd_new_key1 set END_DATE = '2999-12-31' where END_DATE is null;

--

truncate crm_crm1_scd_designgate3_red;

insert into crm_crm1_scd_designgate3_red
select * from crm_crm1_scd_new_key1;

truncate crm_crm2_scd_designgate3_red;

insert into crm_crm2_scd_designgate3_red
select * from crm_crm2_scd_new_key1;

-- for manual run
update file_load_log
set crm =1
where crm=0;

--

drop table if exists crm_relaz;
drop table if exists crm_distinct_relaz;
drop table if exists crm_crm_new__;
drop table if exists ref_x;
drop table if exists crm_relaz_asset_same;
drop table if exists crm_relaz_asset_same_copy;
drop table if exists crm_relaz_asset_diff;
drop table if exists crm_crm_new_;
drop table if exists crm_crm_mapping_;
drop table if exists crm_crm_mapping_copy;
drop table if exists crm_crm_mapping;
drop table if exists ref_x_new;
drop table if exists crm_crm_btn_Mobile;
drop table if exists crm_crm_new;
drop table if exists crm_relaz_no_btn;
drop table if exists crm_crm_mapping_ambito;
drop table if exists crm_crm_mapping_ambito_no_btn;
drop table if exists crm_crm_mapping_ambito_no_btn_not_active;
drop table if exists crm_crm_mapping_ambito_no_btn_only_tiid;
drop table if exists crm_crm_mapping_ambito_no_btn_not_active_only_tiid;
drop table if exists crm_relaz_same_tiid;
drop table if exists crm_relaz_elemento;
drop table if exists crm_relaz_multiple_tiid;
drop table if exists crm_relaz_multiple;
drop table if exists crm_tiid_not_in;
drop table if exists crm_tiid_from_mapping;
drop table if exists crm_crm_final;
drop table if exists crm_crm3_scd_temp;
drop table if exists crm_crm3_scd_temp_rno;
drop table if exists crm_crm1_scd_new_key1;
drop table if exists crm_crm2_scd_new_key1;

end;

################## Table DDL ######################################################################

customer_base

         Column              |            Type             | Collation | Nullable | Default

---------------------------------+-----------------------------+-----------+----------+---------
row_id_asset | bigint | | not null |
offer_name | character varying(255) | | |
prodotto_class | character varying(35) | | |
prodotto_subtype | character varying(255) | | |
relaz_abilitante_abilitato | bigint | | |
invariante | character varying(50) | | |
invariante_ordine | character varying(50) | | |
numero_telefono | character varying(120) | | |
data_prima_attivazione | timestamp without time zone | | |
data_attivazione | timestamp without time zone | | |
data_cessazione_consistenza | timestamp without time zone | | |
data_cessazione_prodotto | timestamp without time zone | | |
ambito | character varying(120) | | |
tipo_prodotto | character varying(300) | | |
metodo_pagamento | character varying(200) | | |
stato_commerciale | character varying(120) | | |
flag_device | character varying(1) | | |
tipo_pagamento | character varying(120) | | |
id_cliente_mdc_org_ext | character varying(60) | | |
codice_fiscale | character varying(200) | | |
sesso | character varying(200) | | |
segmento | character varying(120) | | |
sottosegmento | character varying(120) | | |
ti_id_intestatario | character varying(120) | | |
ti_id_pagatore | character varying(120) | | |
ti_id_utilizzatore | character varying(120) | | |
churn_percentile | double precision | | |
churn_probability | double precision | | |
importo_fattura_fisso | double precision | | |
nr_chiamate_settimanale | integer | | |
data_ini_sett_chiamate | timestamp without time zone | | |
data_fin_sett_chiamate | timestamp without time zone | | |
quantita_byte_settimanale | bigint | | |
data_ini_sett_bytes | timestamp without time zone | | |
data_fin_sett_bytes | timestamp without time zone | | |
internet_tier | bigint | | |
num_tot_rate | character varying(300) | | |
cap | character varying(50) | | |
indirizzo | character varying(100) | | |
data_creazione_tec_asset | timestamp without time zone | | |
data_modifica_tec_asset | timestamp without time zone | | |
hash_value | character varying(2000) | | |
partition_date | date | | |
flag_campione_controllo | character varying(50) | | |
data_prevista_cessazione_sconto | character varying(100) | | |
cic | character varying(50) | | |
cus | character varying(100) | | |
cproductid | character varying(255) | | |
info_cliente | character varying(255) | | |
start_date | date | | |
end_date | date | | |
filename | character varying(100) | | |
Indexes:
"idx_crm_numero_telefono" btree (numero_telefono)
Distributed by: (row_id_asset, end_date)

[NadeemRajput-sys]

Hi @adnanhamdussalam ,

Could you please share the related DDLs and the SP so that we can try to reproduce the issue? Thanks.

Hi @liang8283 waiting any update on this as this is blocking processes. Can you help a little quick one here.