-- requete sur cp
insert into coregistration.export_auto_non_eligible (export_auto_non_eligible_affichage)
select coregistration_coregistration_affichage
from coregistration
where coregistration_campagne_annonceur = 778
and coregistration_champ = 6
and coregistration_eligible = 1
and substring(coregistration_valeur from 1 for 2) not in ('94', '93', '92', '91', '78', '77', '75', '33', '14', '13', '16', '17', '19', '24', '07', '26', '31', '43', '38', '60', '46', '86', '87', '83', '69', '76', '27', '42');
-- on passe les lignes traitees a exportable 3 pour ne plus les selectionner
update coregistration set coregistration_eligible = 3
where coregistration_coregistration_affichage in
(
select coregistration_coregistration_affichage
from coregistration
where coregistration_campagne_annonceur = 778
and coregistration_champ = 6
and coregistration_eligible = 1
and substring(coregistration_valeur from 1 for 2) not in ('94', '93', '92', '91', '78', '77', '75', '33', '14', '13', '16', '17', '19', '24', '07', '26', '31', '43', '38', '60', '46', '86', '87', '83', '69', '76', '27', '42')
);
-- fin requete cp

-- requete sur sous question en fonction question_id / reponse_id et campagne_annonceur_id
insert into coregistration.export_auto_non_eligible (export_auto_non_eligible_affichage)
select coregistration_reponse_affichage
from coregistration.coregistration_reponse
inner join coregistration.coregistration on coregistration_coregistration_affichage = coregistration_reponse_affichage and coregistration_champ = 1 -- jointure sur coreg
inner join coregistration.association_coregistration_question on association_coregistration_question_id_question = coregistration_reponse_id_question
where coregistration_reponse_id_question = 542
and coregistration_reponse_id_reponse = '2236'
and coregistration_campagne_annonceur = 778
and coregistration_eligible = 1;
-- on passe les lignes traités à exportable 3 pour ne plus les selectionner
update coregistration set coregistration_eligible = 3
where coregistration_coregistration_affichage in
(
select coregistration_reponse_affichage
from coregistration.coregistration_reponse
inner join coregistration.coregistration on coregistration_coregistration_affichage = coregistration_reponse_affichage and coregistration_champ = 1 -- jointure sur coreg
inner join coregistration.association_coregistration_question on association_coregistration_question_id_question = coregistration_reponse_id_question
where coregistration_reponse_id_question = 542
and coregistration_reponse_id_reponse = '2236'
and coregistration_campagne_annonceur = 778
and coregistration_eligible = 1
);
-- fin requete sous question