/*	DATE BETWEEN	*/

SELECT * 
FROM CARTOFLEX.dbo.CARTOFLEX_CONTACTS_CLIENT_V CARTOFLEX_CONTACTS_CLIENT_V 
WHERE CARTOFLEX_CONTACTS_CLIENT_V.USERCRDH BETWEEN '2022-01-01 00:00:01' AND '2022-12-31 23:30:00';

/*	Equivalent de limit	*/
SELECT * FROM
(
select *,
ROW_NUMBER() OVER (ORDER BY Truc ASC) AS Rank
FROM T
) X
WHERE Rank BETWEEN 50 AND 100;


AND CARTOFLEX_CONTACTS_PROSPECT_V.NOM AS 'COMPNAME' 

AND (CLI.EMAIL <> '' OR CLI.EMAILCONTACT <> '') 



/*	Colonne fictive avec requête imbriquée	*/

SELECT 
PROSPECT.TIERS, 
T2.TIT, 
(SELECT DISTINCT TABLE_TITRE_RAISON_SOCIALE_V.LIBELLE FROM TABLE_TITRE_RAISON_SOCIALE_V WHERE TABLE_TITRE_RAISON_SOCIALE_V.TITRE = T2.TIT) AS 'CIVILITY' 
FROM CARTOFLEX_CONTACTS_PROSPECT_V PROSPECT, T2 T2 
WHERE T2.TIERS = PROSPECT.TIERS 
AND (PROSPECT.USERCRDH BETWEEN '2022-11-01 00:00:01' AND '2023-03-01 23:30:00') 



SELECT CARTOFLEX_CONTACTS_PROSPECT_V.NOMTIERS, CARTOFLEX_CONTACTS_PROSPECT_V.TIERS, CARTOFLEX_CONTACTS_PROSPECT_V.VIL, CARTOFLEX_CONTACTS_PROSPECT_V.USERCRDH, CARTOFLEX_CONTACTS_PROSPECT_V.ORIGCOD 
FROM CARTOFLEX.dbo.CARTOFLEX_CONTACTS_PROSPECT_V CARTOFLEX_CONTACTS_PROSPECT_V
WHERE (CARTOFLEX_CONTACTS_PROSPECT_V.USERCRDH BETWEEN '2022-01-01 00:00:01' AND '2022-12-31 23:30:00');
UNION 
SELECT CARTOFLEX_CONTACTS_CLIENT_V.NOMTIERS, CARTOFLEX_CONTACTS_CLIENT_V.TIERS, CARTOFLEX_CONTACTS_CLIENT_V.VIL, CARTOFLEX_CONTACTS_CLIENT_V.USERCRDH, CARTOFLEX_CONTACTS_CLIENT_V.ORIGCOD 
FROM CARTOFLEX.dbo.CARTOFLEX_CONTACTS_CLIENT_V CARTOFLEX_CONTACTS_CLIENT_V
WHERE (CARTOFLEX_CONTACTS_CLIENT_V.USERCRDH BETWEEN '2022-01-01 00:00:01' AND '2022-12-31 23:30:00');




SELECT CARTOFLEX_CONTACTS_CLIENT_V.NOMTIERS, CARTOFLEX_CONTACTS_CLIENT_V.TIERS, CARTOFLEX_CONTACTS_CLIENT_V.VIL, CARTOFLEX_CONTACTS_CLIENT_V.USERCRDH, CARTOFLEX_CONTACTS_CLIENT_V.ORIGCOD 
FROM CARTOFLEX.dbo.CARTOFLEX_CONTACTS_CLIENT_V CARTOFLEX_CONTACTS_CLIENT_V 
WHERE (CARTOFLEX_CONTACTS_CLIENT_V.NOMTIERS LIKE 'SILO%') AND (CARTOFLEX_CONTACTS_CLIENT_V.USERCRDH BETWEEN '2023-01-01 00:00:01' AND '2023-02-28 23:30:00');



SELECT CARTOFLEX_CONTACTS_PROSPECT_V.NOMTIERS, CARTOFLEX_CONTACTS_PROSPECT_V.TIERS, CARTOFLEX_CONTACTS_PROSPECT_V.VIL, CARTOFLEX_CONTACTS_PROSPECT_V.USERCRDH, CARTOFLEX_CONTACTS_PROSPECT_V.ORIGCOD, 
CARTOFLEX_CONTACTS_PROSPECT_V.RUE, CARTOFLEX_CONTACTS_PROSPECT_V.LOC, CARTOFLEX_CONTACTS_PROSPECT_V.VIL, CARTOFLEX_CONTACTS_PROSPECT_V.CPOSTAL 
FROM CARTOFLEX.dbo.CARTOFLEX_CONTACTS_PROSPECT_V CARTOFLEX_CONTACTS_PROSPECT_V
WHERE CARTOFLEX_CONTACTS_PROSPECT_V.TIERS IN ('P0014538', 'P0016728');


SELECT CARTOFLEX_CONTACTS_PROSPECT_V.TIERS, CARTOFLEX_CONTACTS_PROSPECT_V.NOMTIERS, CARTOFLEX_CONTACTS_PROSPECT_V.RUE, CARTOFLEX_CONTACTS_PROSPECT_V.LOC, CARTOFLEX_CONTACTS_PROSPECT_V.VIL, CARTOFLEX_CONTACTS_PROSPECT_V.CPOSTAL, CARTOFLEX_CONTACTS_PROSPECT_V.ORIGCOD, 
CARTOFLEX_CONTACTS_PROSPECT_V.CONTACT, CARTOFLEX_CONTACTS_PROSPECT_V.NOMCOMTACT, CARTOFLEX_CONTACTS_PROSPECT_V."RAISON SOCIAL PROSPECT", CARTOFLEX_CONTACTS_PROSPECT_V.NOM  
FROM CARTOFLEX.dbo.CARTOFLEX_CONTACTS_PROSPECT_V CARTOFLEX_CONTACTS_PROSPECT_V
WHERE CARTOFLEX_CONTACTS_PROSPECT_V.TIERS IN ('P0013494','P0008524','C0003638','C0010942') 
UNION 
SELECT CARTOFLEX_CONTACTS_CLIENT_V.TIERS, CARTOFLEX_CONTACTS_CLIENT_V.NOMTIERS, CARTOFLEX_CONTACTS_CLIENT_V.RUE, CARTOFLEX_CONTACTS_CLIENT_V.LOC, CARTOFLEX_CONTACTS_CLIENT_V.VIL, CARTOFLEX_CONTACTS_CLIENT_V.CPOSTAL, CARTOFLEX_CONTACTS_CLIENT_V.ORIGCOD, 
CARTOFLEX_CONTACTS_CLIENT_V.CONTACT, CARTOFLEX_CONTACTS_CLIENT_V.NOMCOMTACT, CARTOFLEX_CONTACTS_CLIENT_V."RAISON SOCIAL CLIENT", CARTOFLEX_CONTACTS_CLIENT_V.NOM  
FROM CARTOFLEX.dbo.CARTOFLEX_CONTACTS_CLIENT_V CARTOFLEX_CONTACTS_CLIENT_V
WHERE CARTOFLEX_CONTACTS_CLIENT_V.TIERS IN ('P0013494','P0008524','C0003638','C0010942') 



/*	TESTS	*/
SELECT CLIENT.TIERS, CLIENT.EMAIL, CLIENT.EMAILCONTACT
FROM CARTOFLEX.dbo.CARTOFLEX_CONTACTS_CLIENT_V CLIENT 
WHERE CLIENT.TIERS = 'C0003009'

SELECT CLIENT.TIERS, CLIENT.EMAIL 
FROM CARTOFLEX.dbo.CLI CLIENT 
WHERE CLIENT.TIERS = 'C0003009'




/*	CASE	*/
SELECT T2.EMAIL AS 'EMAIL', PRO.TIERS AS 'TIERS', TABLE_TITRE_RAISON_SOCIALE_V.LIBELLE AS 'COMPNAME_PREFIX', PRO.NOM AS 'COMPNAME', PRO.RUE AS 'ADDRESS_1', PRO.LOC AS 'ADDRESS_2', '' AS 'ADDRESS_3', PRO.CPOSTAL AS 'POSTCODE', PRO.VIL AS 'CITY', 
PRO.TEL AS 'PHONE', 
  CASE T2.TIT 
	 WHEN 'MR' THEN 'Monsieur' 
	 WHEN 'MME' THEN 'Madame' 
  ELSE '' END AS 'CIVILITY', 
T2.PRENOM AS 'SURNAME', T2.NOM AS 'NOM', '' AS 'CRYPTO', 'CARTE' AS 'SECTEUR', 'Agnès CORDA' AS 'COMMERCIAL', 'PROSPECT' AS 'MATURITY', '' AS 'ZONE', '' AS 'ORDER_STATUS',
'PROMO_20%_P1' AS 'CAMPAIGN_NAME', 'votre curiosité récompensée' AS 'CUSTOM_SUBJECT', 
  CASE PRO.TIT 
	 WHEN 'VID' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN '620' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN 'ASS' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN 'BIB' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN 'BDE' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN '344' THEN 'COL;élèves;scolaires' 
	 WHEN '600' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN '340' THEN 'COL;élèves;scolaires' 
	 WHEN '350' THEN 'COL;élèves;scolaires' 
	 WHEN '341' THEN 'COL;élèves;scolaires' 
	 WHEN '010' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN '380' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN 'ECO' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN '430' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN '154' THEN 'COL;élèves;scolaires' 
	 WHEN '342' THEN 'COL;élèves;scolaires' 
	 WHEN '030' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN '020' THEN 'COL;élèves;scolaires' 
	 WHEN '040' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN 'INST' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN '300' THEN 'COL;élèves;scolaires' 
	 WHEN '322' THEN 'COL;élèves;scolaires' 
	 WHEN '310' THEN 'COL;élèves;scolaires' 
	 WHEN '302' THEN 'COL;élèves;scolaires' 
	 WHEN '303' THEN 'COL;élèves;scolaires' 
	 WHEN '301' THEN 'COL;élèves;scolaires' 
	 WHEN '306' THEN 'COL;élèves;scolaires' 
	 WHEN '309' THEN 'COL;élèves;scolaires' 
	 WHEN '308' THEN 'COL;élèves;scolaires' 
	 WHEN '320' THEN 'COL;élèves;scolaires' 
	 WHEN '319' THEN 'COL;élèves;scolaires' 
	 WHEN '321' THEN 'COL;élèves;scolaires' 
	 WHEN '523' THEN 'SUP;étudiants;d’étudiants' 
  ELSE 'SUP' END AS 'DOMAIN;CUSTOM_CARTE_1;CUSTOM_CARTE_2' 
FROM CARTOFLEX.dbo.ENT ENT, CARTOFLEX.dbo.PRO PRO, CARTOFLEX.dbo.T2 T2, CARTOFLEX.dbo.TABLE_TITRE_RAISON_SOCIALE_V TABLE_TITRE_RAISON_SOCIALE_V
WHERE PRO.DOS = T2.DOS AND T2.TIERS = PRO.TIERS AND TABLE_TITRE_RAISON_SOCIALE_V.DOSC = PRO.DOS AND TABLE_TITRE_RAISON_SOCIALE_V.TITRE = PRO.TIT AND PRO.DOS = ENT.DOS AND PRO.TIERS = ENT.TIERS AND PRO.DOS='1' AND PRO.REPR_0001 = 'ACS' AND T2.EMAIL <> '' 
UNION 
SELECT PRO.EMAIL AS 'EMAIL', PRO.TIERS AS 'TIERS', TABLE_TITRE_RAISON_SOCIALE_V.LIBELLE AS 'COMPNAME_PREFIX', PRO.NOM AS 'COMPNAME', PRO.RUE AS 'ADDRESS_1', PRO.LOC AS 'ADDRESS_2', '' AS 'ADDRESS_3', PRO.CPOSTAL AS 'POSTCODE', PRO.VIL AS 'CITY', 
PRO.TEL AS 'PHONE', 
  CASE T2.TIT 
	 WHEN 'MR' THEN 'Monsieur' 
	 WHEN 'MME' THEN 'Madame' 
  ELSE '' END AS 'CIVILITY', 
T2.PRENOM AS 'SURNAME', T2.NOM AS 'NOM', '' AS 'CRYPTO', 'CARTE' AS 'SECTEUR', 'Agnès CORDA' AS 'COMMERCIAL', 'PROSPECT' AS 'MATURITY', '' AS 'ZONE', '' AS 'ORDER_STATUS',
'PROMO_20%_P1' AS 'CAMPAIGN_NAME', 'votre curiosité récompensée' AS 'CUSTOM_SUBJECT', 
  CASE PRO.TIT 
	 WHEN 'VID' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN '620' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN 'ASS' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN 'BIB' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN 'BDE' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN '344' THEN 'COL;élèves;scolaires' 
	 WHEN '600' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN '340' THEN 'COL;élèves;scolaires' 
	 WHEN '350' THEN 'COL;élèves;scolaires' 
	 WHEN '341' THEN 'COL;élèves;scolaires' 
	 WHEN '010' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN '380' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN 'ECO' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN '430' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN '154' THEN 'COL;élèves;scolaires' 
	 WHEN '342' THEN 'COL;élèves;scolaires' 
	 WHEN '030' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN '020' THEN 'COL;élèves;scolaires' 
	 WHEN '040' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN 'INST' THEN 'SUP;étudiants;d’étudiants' 
	 WHEN '300' THEN 'COL;élèves;scolaires' 
	 WHEN '322' THEN 'COL;élèves;scolaires' 
	 WHEN '310' THEN 'COL;élèves;scolaires' 
	 WHEN '302' THEN 'COL;élèves;scolaires' 
	 WHEN '303' THEN 'COL;élèves;scolaires' 
	 WHEN '301' THEN 'COL;élèves;scolaires' 
	 WHEN '306' THEN 'COL;élèves;scolaires' 
	 WHEN '309' THEN 'COL;élèves;scolaires' 
	 WHEN '308' THEN 'COL;élèves;scolaires' 
	 WHEN '320' THEN 'COL;élèves;scolaires' 
	 WHEN '319' THEN 'COL;élèves;scolaires' 
	 WHEN '321' THEN 'COL;élèves;scolaires' 
	 WHEN '523' THEN 'SUP;étudiants;d’étudiants' 
  ELSE 'SUP' END AS 'DOMAIN;CUSTOM_CARTE_1;CUSTOM_CARTE_2' 
FROM CARTOFLEX.dbo.ENT ENT, CARTOFLEX.dbo.PRO PRO, CARTOFLEX.dbo.T2 T2, CARTOFLEX.dbo.TABLE_TITRE_RAISON_SOCIALE_V TABLE_TITRE_RAISON_SOCIALE_V
WHERE PRO.DOS = T2.DOS AND T2.TIERS = PRO.TIERS AND TABLE_TITRE_RAISON_SOCIALE_V.DOSC = PRO.DOS AND TABLE_TITRE_RAISON_SOCIALE_V.TITRE = PRO.TIT AND PRO.DOS = ENT.DOS AND PRO.TIERS = ENT.TIERS AND PRO.DOS='1' AND PRO.REPR_0001 = 'ACS' AND PRO.EMAIL <> ''









--	Query extraction clients emailings enseignement
SELECT T2.EMAIL AS 'EMAIL', PRO.TIERS AS 'TIERS', PRO.NOM AS 'COMPNAME', PRO.RUE AS 'ADDRESS_1', PRO.LOC AS 'ADDRESS_2', '' AS 'ADDRESS_3', PRO.CPOSTAL AS 'POSTCODE', PRO.VIL AS 'CITY', PRO.TEL AS 'PHONE', 
  CASE T2.TIT 
	 WHEN 'MR' THEN 'Monsieur' 
	 WHEN 'MME' THEN 'Madame' 
  ELSE '' END AS 'CIVILITY', 
T2.PRENOM AS 'SURNAME', T2.NOM AS 'NOM', '' AS 'CRYPTO', 'CARTE' AS 'SECTEUR', 'Agnès CORDA' AS 'COMMERCIAL', 'PROSPECT' AS 'MATURITY', '' AS 'ZONE', '' AS 'ORDER_STATUS', 
  CASE PRO.TIT 
	 WHEN '010' THEN 'CONSERVATOIRE;SUP;étudiants;d’étudiants' 
	 WHEN '020' THEN 'GROUPE SCOLAIRE;COL;élèves;scolaires' 
	 WHEN '030' THEN 'GRETA;SUP;étudiants;d’étudiants' 
	 WHEN '040' THEN 'IFSI;SUP;étudiants;d’étudiants' 
	 WHEN '154' THEN 'ECOLE PRIMAIRE;COL;élèves;scolaires' 
	 WHEN '300' THEN 'LEGT;COL;élèves;scolaires' 
	 WHEN '301' THEN 'LYCEE D ENSEIGNEMENT TECHNOLOGIQUE;COL;élèves;scolaires' 
	 WHEN '302' THEN 'LYCEE D ENSEIGNEMENT GENERAL;COL;élèves;scolaires' 
	 WHEN '303' THEN 'LYCEE D ENSEIGNEMENT GENERAL PRIVE;COL;élèves;scolaires' 
	 WHEN '306' THEN 'LYCEE POLYVALENT;COL;élèves;scolaires' 
	 WHEN '308' THEN 'LYCEE POLYVALENT PRIVE;COL;élèves;scolaires' 
	 WHEN '309' THEN 'LYCEE POLYVALENT DES METIERS;COL;élèves;scolaires' 
	 WHEN '310' THEN 'LYCEE CLIMATIQUE;COL;élèves;scolaires' 
	 WHEN '319' THEN 'LYCEE PROFESSIONNEL DES METIERS;COL;élèves;scolaires' 
	 WHEN '320' THEN 'LYCEE PROFESSIONNEL;COL;élèves;scolaires' 
	 WHEN '321' THEN 'LYCEE PROFESSIONNEL PRIVE;COL;élèves;scolaires' 
	 WHEN '322' THEN 'LYCEE;COL;élèves;scolaires' 
	 WHEN '340' THEN 'COLLEGE;COL;élèves;scolaires' 
	 WHEN '341' THEN 'COLLEGE PRIVE;COL;élèves;scolaires' 
	 WHEN '342' THEN 'GOD;COL;élèves;scolaires' 
	 WHEN '344' THEN 'CETAD;COL;élèves;scolaires' 
	 WHEN '350' THEN 'COLLEGE CLIMATIQUE;COL;élèves;scolaires' 
	 WHEN '380' THEN 'CTR FORMATION PROF. AGRICOLE;SUP;étudiants;d’étudiants' 
	 WHEN '430' THEN 'ECOLE DE FORMATION SANITAIRE ET SOCIALE;SUP;étudiants;d’étudiants' 
	 WHEN '523' THEN 'UNIVERSITE;SUP;étudiants;d’étudiants' 
	 WHEN '600' THEN 'CFA;SUP;étudiants;d’étudiants' 
	 WHEN '620' THEN 'ANNEXE DE CFA;SUP;étudiants;d’étudiants' 
	 WHEN 'ASS' THEN 'ASSOCIATION;SUP;étudiants;d’étudiants' 
	 WHEN 'BDE' THEN 'BUREAU DES ELEVES;SUP;étudiants;d’étudiants' 
	 WHEN 'BIB' THEN 'BIBLIOTHEQUE;SUP;étudiants;d’étudiants' 
	 WHEN 'ECO' THEN 'ECOLE;SUP;étudiants;d’étudiants' 
	 WHEN 'EXT' THEN 'EXTERNAT;SUP;étudiants;d’étudiants' 
	 WHEN 'INST' THEN 'INSTITUT;SUP;étudiants;d’étudiants' 
	 WHEN 'MAI' THEN 'MAIRIE;SUP;étudiants;d’étudiants' 
	 WHEN 'MLLE' THEN 'Mademoiselle;SUP;étudiants;d’étudiants' 
	 WHEN 'MME' THEN 'Madame;SUP;étudiants;d’étudiants' 
	 WHEN 'MR' THEN 'Monsieur;SUP;étudiants;d’étudiants' 
	 WHEN 'VID' THEN ';SUP;étudiants;d’étudiants' 
  ELSE '' END AS 'COMPNAME_PREFIX;DOMAIN;CUSTOM_CARTE_1;CUSTOM_CARTE_2' 
FROM CARTOFLEX.dbo.PRO PRO, CARTOFLEX.dbo.T2 T2 
WHERE PRO.DOS = T2.DOS AND T2.TIERS = PRO.TIERS AND PRO.DOS='1' AND PRO.REPR_0001 = 'ACS' AND T2.EMAIL <> '' 
UNION 
SELECT PROSPECT.EMAILCONTACT AS 'EMAIL', PRO.TIERS AS 'TIERS', PRO.NOM AS 'COMPNAME', PRO.RUE AS 'ADDRESS_1', PRO.LOC AS 'ADDRESS_2', '' AS 'ADDRESS_3', PRO.CPOSTAL AS 'POSTCODE', PRO.VIL AS 'CITY', PRO.TEL AS 'PHONE', 
  CASE T2.TIT 
	 WHEN 'MR' THEN 'Monsieur' 
	 WHEN 'MME' THEN 'Madame' 
  ELSE '' END AS 'CIVILITY', 
T2.PRENOM AS 'SURNAME', T2.NOM AS 'NOM', '' AS 'CRYPTO', 'CARTE' AS 'SECTEUR', 'Agnès CORDA' AS 'COMMERCIAL', 'PROSPECT' AS 'MATURITY', '' AS 'ZONE', '' AS 'ORDER_STATUS',
  CASE PRO.TIT 
	 WHEN '010' THEN 'CONSERVATOIRE;SUP;étudiants;d’étudiants' 
	 WHEN '020' THEN 'GROUPE SCOLAIRE;COL;élèves;scolaires' 
	 WHEN '030' THEN 'GRETA;SUP;étudiants;d’étudiants' 
	 WHEN '040' THEN 'IFSI;SUP;étudiants;d’étudiants' 
	 WHEN '154' THEN 'ECOLE PRIMAIRE;COL;élèves;scolaires' 
	 WHEN '300' THEN 'LEGT;COL;élèves;scolaires' 
	 WHEN '301' THEN 'LYCEE D ENSEIGNEMENT TECHNOLOGIQUE;COL;élèves;scolaires' 
	 WHEN '302' THEN 'LYCEE D ENSEIGNEMENT GENERAL;COL;élèves;scolaires' 
	 WHEN '303' THEN 'LYCEE D ENSEIGNEMENT GENERAL PRIVE;COL;élèves;scolaires' 
	 WHEN '306' THEN 'LYCEE POLYVALENT;COL;élèves;scolaires' 
	 WHEN '308' THEN 'LYCEE POLYVALENT PRIVE;COL;élèves;scolaires' 
	 WHEN '309' THEN 'LYCEE POLYVALENT DES METIERS;COL;élèves;scolaires' 
	 WHEN '310' THEN 'LYCEE CLIMATIQUE;COL;élèves;scolaires' 
	 WHEN '319' THEN 'LYCEE PROFESSIONNEL DES METIERS;COL;élèves;scolaires' 
	 WHEN '320' THEN 'LYCEE PROFESSIONNEL;COL;élèves;scolaires' 
	 WHEN '321' THEN 'LYCEE PROFESSIONNEL PRIVE;COL;élèves;scolaires' 
	 WHEN '322' THEN 'LYCEE;COL;élèves;scolaires' 
	 WHEN '340' THEN 'COLLEGE;COL;élèves;scolaires' 
	 WHEN '341' THEN 'COLLEGE PRIVE;COL;élèves;scolaires' 
	 WHEN '342' THEN 'GOD;COL;élèves;scolaires' 
	 WHEN '344' THEN 'CETAD;COL;élèves;scolaires' 
	 WHEN '350' THEN 'COLLEGE CLIMATIQUE;COL;élèves;scolaires' 
	 WHEN '380' THEN 'CTR FORMATION PROF. AGRICOLE;SUP;étudiants;d’étudiants' 
	 WHEN '430' THEN 'ECOLE DE FORMATION SANITAIRE ET SOCIALE;SUP;étudiants;d’étudiants' 
	 WHEN '523' THEN 'UNIVERSITE;SUP;étudiants;d’étudiants' 
	 WHEN '600' THEN 'CFA;SUP;étudiants;d’étudiants' 
	 WHEN '620' THEN 'ANNEXE DE CFA;SUP;étudiants;d’étudiants' 
	 WHEN 'ASS' THEN 'ASSOCIATION;SUP;étudiants;d’étudiants' 
	 WHEN 'BDE' THEN 'BUREAU DES ELEVES;SUP;étudiants;d’étudiants' 
	 WHEN 'BIB' THEN 'BIBLIOTHEQUE;SUP;étudiants;d’étudiants' 
	 WHEN 'ECO' THEN 'ECOLE;SUP;étudiants;d’étudiants' 
	 WHEN 'EXT' THEN 'EXTERNAT;SUP;étudiants;d’étudiants' 
	 WHEN 'INST' THEN 'INSTITUT;SUP;étudiants;d’étudiants' 
	 WHEN 'MAI' THEN 'MAIRIE;SUP;étudiants;d’étudiants' 
	 WHEN 'MLLE' THEN 'Mademoiselle;SUP;étudiants;d’étudiants' 
	 WHEN 'MME' THEN 'Madame;SUP;étudiants;d’étudiants' 
	 WHEN 'MR' THEN 'Monsieur;SUP;étudiants;d’étudiants' 
	 WHEN 'VID' THEN ';SUP;étudiants;d’étudiants' 
  ELSE '' END AS 'COMPNAME_PREFIX;DOMAIN;CUSTOM_CARTE_1;CUSTOM_CARTE_2' 
FROM CARTOFLEX.dbo.PRO PRO, CARTOFLEX.dbo.T2 T2, CARTOFLEX.dbo.CARTOFLEX_CONTACTS_PROSPECT_V PROSPECT 
WHERE PRO.DOS = T2.DOS 
AND PROSPECT.TIERS = PRO.TIERS 
AND PROSPECT.TIERS = T2.TIERS 
AND T2.TIERS = PRO.TIERS AND PRO.DOS='1' AND PRO.REPR_0001 = 'ACS' AND PROSPECT.EMAILCONTACT <> ''
UNION 
SELECT PRO.EMAIL AS 'EMAIL', PRO.TIERS AS 'TIERS', PRO.NOM AS 'COMPNAME', PRO.RUE AS 'ADDRESS_1', PRO.LOC AS 'ADDRESS_2', '' AS 'ADDRESS_3', PRO.CPOSTAL AS 'POSTCODE', PRO.VIL AS 'CITY', 
PRO.TEL AS 'PHONE', 
  CASE T2.TIT 
	 WHEN 'MR' THEN 'Monsieur' 
	 WHEN 'MME' THEN 'Madame' 
  ELSE '' END AS 'CIVILITY', 
T2.PRENOM AS 'SURNAME', T2.NOM AS 'NOM', '' AS 'CRYPTO', 'CARTE' AS 'SECTEUR', 'Agnès CORDA' AS 'COMMERCIAL', 'PROSPECT' AS 'MATURITY', '' AS 'ZONE', '' AS 'ORDER_STATUS', 
  CASE PRO.TIT 
	 WHEN '010' THEN 'CONSERVATOIRE;SUP;étudiants;d’étudiants' 
	 WHEN '020' THEN 'GROUPE SCOLAIRE;COL;élèves;scolaires' 
	 WHEN '030' THEN 'GRETA;SUP;étudiants;d’étudiants' 
	 WHEN '040' THEN 'IFSI;SUP;étudiants;d’étudiants' 
	 WHEN '154' THEN 'ECOLE PRIMAIRE;COL;élèves;scolaires' 
	 WHEN '300' THEN 'LEGT;COL;élèves;scolaires' 
	 WHEN '301' THEN 'LYCEE D ENSEIGNEMENT TECHNOLOGIQUE;COL;élèves;scolaires' 
	 WHEN '302' THEN 'LYCEE D ENSEIGNEMENT GENERAL;COL;élèves;scolaires' 
	 WHEN '303' THEN 'LYCEE D ENSEIGNEMENT GENERAL PRIVE;COL;élèves;scolaires' 
	 WHEN '306' THEN 'LYCEE POLYVALENT;COL;élèves;scolaires' 
	 WHEN '308' THEN 'LYCEE POLYVALENT PRIVE;COL;élèves;scolaires' 
	 WHEN '309' THEN 'LYCEE POLYVALENT DES METIERS;COL;élèves;scolaires' 
	 WHEN '310' THEN 'LYCEE CLIMATIQUE;COL;élèves;scolaires' 
	 WHEN '319' THEN 'LYCEE PROFESSIONNEL DES METIERS;COL;élèves;scolaires' 
	 WHEN '320' THEN 'LYCEE PROFESSIONNEL;COL;élèves;scolaires' 
	 WHEN '321' THEN 'LYCEE PROFESSIONNEL PRIVE;COL;élèves;scolaires' 
	 WHEN '322' THEN 'LYCEE;COL;élèves;scolaires' 
	 WHEN '340' THEN 'COLLEGE;COL;élèves;scolaires' 
	 WHEN '341' THEN 'COLLEGE PRIVE;COL;élèves;scolaires' 
	 WHEN '342' THEN 'GOD;COL;élèves;scolaires' 
	 WHEN '344' THEN 'CETAD;COL;élèves;scolaires' 
	 WHEN '350' THEN 'COLLEGE CLIMATIQUE;COL;élèves;scolaires' 
	 WHEN '380' THEN 'CTR FORMATION PROF. AGRICOLE;SUP;étudiants;d’étudiants' 
	 WHEN '430' THEN 'ECOLE DE FORMATION SANITAIRE ET SOCIALE;SUP;étudiants;d’étudiants' 
	 WHEN '523' THEN 'UNIVERSITE;SUP;étudiants;d’étudiants' 
	 WHEN '600' THEN 'CFA;SUP;étudiants;d’étudiants' 
	 WHEN '620' THEN 'ANNEXE DE CFA;SUP;étudiants;d’étudiants' 
	 WHEN 'ASS' THEN 'ASSOCIATION;SUP;étudiants;d’étudiants' 
	 WHEN 'BDE' THEN 'BUREAU DES ELEVES;SUP;étudiants;d’étudiants' 
	 WHEN 'BIB' THEN 'BIBLIOTHEQUE;SUP;étudiants;d’étudiants' 
	 WHEN 'ECO' THEN 'ECOLE;SUP;étudiants;d’étudiants' 
	 WHEN 'EXT' THEN 'EXTERNAT;SUP;étudiants;d’étudiants' 
	 WHEN 'INST' THEN 'INSTITUT;SUP;étudiants;d’étudiants' 
	 WHEN 'MAI' THEN 'MAIRIE;SUP;étudiants;d’étudiants' 
	 WHEN 'MLLE' THEN 'Mademoiselle;SUP;étudiants;d’étudiants' 
	 WHEN 'MME' THEN 'Madame;SUP;étudiants;d’étudiants' 
	 WHEN 'MR' THEN 'Monsieur;SUP;étudiants;d’étudiants' 
	 WHEN 'VID' THEN ';SUP;étudiants;d’étudiants' 
  ELSE '' END AS 'COMPNAME_PREFIX;DOMAIN;CUSTOM_CARTE_1;CUSTOM_CARTE_2' 
FROM CARTOFLEX.dbo.PRO PRO, CARTOFLEX.dbo.T2 T2 
WHERE PRO.DOS = T2.DOS AND T2.TIERS = PRO.TIERS AND PRO.DOS='1' AND PRO.REPR_0001 = 'ACS' AND PRO.EMAIL <> '' 
UNION 
SELECT T2.EMAIL AS 'EMAIL', CLI.TIERS AS 'TIERS', CLI.NOM AS 'COMPNAME', CLI.RUE AS 'ADDRESS_1', CLI.LOC AS 'ADDRESS_2', '' AS 'ADDRESS_3', CLI.CPOSTAL AS 'POSTCODE', CLI.VIL AS 'CITY', CLI.TEL AS 'PHONE', 
  CASE T2.TIT 
	 WHEN 'MR' THEN 'Monsieur' 
	 WHEN 'MME' THEN 'Madame' 
  ELSE '' END AS 'CIVILITY', 
T2.PRENOM AS 'SURNAME', T2.NOM AS 'NOM', '' AS 'CRYPTO', 'CARTE' AS 'SECTEUR', 'Agnès CORDA' AS 'COMMERCIAL', 'CLIENT' AS 'MATURITY', '' AS 'ZONE', '' AS 'ORDER_STATUS',
  CASE CLI.TIT 
	 WHEN '010' THEN 'CONSERVATOIRE;SUP;étudiants;d’étudiants' 
	 WHEN '020' THEN 'GROUPE SCOLAIRE;COL;élèves;scolaires' 
	 WHEN '030' THEN 'GRETA;SUP;étudiants;d’étudiants' 
	 WHEN '040' THEN 'IFSI;SUP;étudiants;d’étudiants' 
	 WHEN '154' THEN 'ECOLE PRIMAIRE;COL;élèves;scolaires' 
	 WHEN '300' THEN 'LEGT;COL;élèves;scolaires' 
	 WHEN '301' THEN 'LYCEE D ENSEIGNEMENT TECHNOLOGIQUE;COL;élèves;scolaires' 
	 WHEN '302' THEN 'LYCEE D ENSEIGNEMENT GENERAL;COL;élèves;scolaires' 
	 WHEN '303' THEN 'LYCEE D ENSEIGNEMENT GENERAL PRIVE;COL;élèves;scolaires' 
	 WHEN '306' THEN 'LYCEE POLYVALENT;COL;élèves;scolaires' 
	 WHEN '308' THEN 'LYCEE POLYVALENT PRIVE;COL;élèves;scolaires' 
	 WHEN '309' THEN 'LYCEE POLYVALENT DES METIERS;COL;élèves;scolaires' 
	 WHEN '310' THEN 'LYCEE CLIMATIQUE;COL;élèves;scolaires' 
	 WHEN '319' THEN 'LYCEE PROFESSIONNEL DES METIERS;COL;élèves;scolaires' 
	 WHEN '320' THEN 'LYCEE PROFESSIONNEL;COL;élèves;scolaires' 
	 WHEN '321' THEN 'LYCEE PROFESSIONNEL PRIVE;COL;élèves;scolaires' 
	 WHEN '322' THEN 'LYCEE;COL;élèves;scolaires' 
	 WHEN '340' THEN 'COLLEGE;COL;élèves;scolaires' 
	 WHEN '341' THEN 'COLLEGE PRIVE;COL;élèves;scolaires' 
	 WHEN '342' THEN 'GOD;COL;élèves;scolaires' 
	 WHEN '344' THEN 'CETAD;COL;élèves;scolaires' 
	 WHEN '350' THEN 'COLLEGE CLIMATIQUE;COL;élèves;scolaires' 
	 WHEN '380' THEN 'CTR FORMATION PROF. AGRICOLE;SUP;étudiants;d’étudiants' 
	 WHEN '430' THEN 'ECOLE DE FORMATION SANITAIRE ET SOCIALE;SUP;étudiants;d’étudiants' 
	 WHEN '523' THEN 'UNIVERSITE;SUP;étudiants;d’étudiants' 
	 WHEN '600' THEN 'CFA;SUP;étudiants;d’étudiants' 
	 WHEN '620' THEN 'ANNEXE DE CFA;SUP;étudiants;d’étudiants' 
	 WHEN 'ASS' THEN 'ASSOCIATION;SUP;étudiants;d’étudiants' 
	 WHEN 'BDE' THEN 'BUREAU DES ELEVES;SUP;étudiants;d’étudiants' 
	 WHEN 'BIB' THEN 'BIBLIOTHEQUE;SUP;étudiants;d’étudiants' 
	 WHEN 'ECO' THEN 'ECOLE;SUP;étudiants;d’étudiants' 
	 WHEN 'EXT' THEN 'EXTERNAT;SUP;étudiants;d’étudiants' 
	 WHEN 'INST' THEN 'INSTITUT;SUP;étudiants;d’étudiants' 
	 WHEN 'MAI' THEN 'MAIRIE;SUP;étudiants;d’étudiants' 
	 WHEN 'MLLE' THEN 'Mademoiselle;SUP;étudiants;d’étudiants' 
	 WHEN 'MME' THEN 'Madame;SUP;étudiants;d’étudiants' 
	 WHEN 'MR' THEN 'Monsieur;SUP;étudiants;d’étudiants' 
	 WHEN 'VID' THEN ';SUP;étudiants;d’étudiants' 
  ELSE '' END AS 'COMPNAME_PREFIX;DOMAIN;CUSTOM_CARTE_1;CUSTOM_CARTE_2' 
FROM CARTOFLEX.dbo.CLI CLI, CARTOFLEX.dbo.T2 T2 
WHERE CLI.DOS = T2.DOS AND T2.TIERS = CLI.TIERS AND CLI.DOS='1' AND CLI.REPR_0001 = 'ACS' AND T2.EMAIL <> '' 
UNION 
SELECT CLI.EMAIL AS 'EMAIL', CLI.TIERS AS 'TIERS', CLI.NOM AS 'COMPNAME', CLI.RUE AS 'ADDRESS_1', CLI.LOC AS 'ADDRESS_2', '' AS 'ADDRESS_3', CLI.CPOSTAL AS 'POSTCODE', CLI.VIL AS 'CITY', 
CLI.TEL AS 'PHONE', 
  CASE T2.TIT 
	 WHEN 'MR' THEN 'Monsieur' 
	 WHEN 'MME' THEN 'Madame' 
  ELSE '' END AS 'CIVILITY', 
T2.PRENOM AS 'SURNAME', T2.NOM AS 'NOM', '' AS 'CRYPTO', 'CARTE' AS 'SECTEUR', 'Agnès CORDA' AS 'COMMERCIAL', 'CLIENT' AS 'MATURITY', '' AS 'ZONE', '' AS 'ORDER_STATUS',
  CASE CLI.TIT 
	 WHEN '010' THEN 'CONSERVATOIRE;SUP;étudiants;d’étudiants' 
	 WHEN '020' THEN 'GROUPE SCOLAIRE;COL;élèves;scolaires' 
	 WHEN '030' THEN 'GRETA;SUP;étudiants;d’étudiants' 
	 WHEN '040' THEN 'IFSI;SUP;étudiants;d’étudiants' 
	 WHEN '154' THEN 'ECOLE PRIMAIRE;COL;élèves;scolaires' 
	 WHEN '300' THEN 'LEGT;COL;élèves;scolaires' 
	 WHEN '301' THEN 'LYCEE D ENSEIGNEMENT TECHNOLOGIQUE;COL;élèves;scolaires' 
	 WHEN '302' THEN 'LYCEE D ENSEIGNEMENT GENERAL;COL;élèves;scolaires' 
	 WHEN '303' THEN 'LYCEE D ENSEIGNEMENT GENERAL PRIVE;COL;élèves;scolaires' 
	 WHEN '306' THEN 'LYCEE POLYVALENT;COL;élèves;scolaires' 
	 WHEN '308' THEN 'LYCEE POLYVALENT PRIVE;COL;élèves;scolaires' 
	 WHEN '309' THEN 'LYCEE POLYVALENT DES METIERS;COL;élèves;scolaires' 
	 WHEN '310' THEN 'LYCEE CLIMATIQUE;COL;élèves;scolaires' 
	 WHEN '319' THEN 'LYCEE PROFESSIONNEL DES METIERS;COL;élèves;scolaires' 
	 WHEN '320' THEN 'LYCEE PROFESSIONNEL;COL;élèves;scolaires' 
	 WHEN '321' THEN 'LYCEE PROFESSIONNEL PRIVE;COL;élèves;scolaires' 
	 WHEN '322' THEN 'LYCEE;COL;élèves;scolaires' 
	 WHEN '340' THEN 'COLLEGE;COL;élèves;scolaires' 
	 WHEN '341' THEN 'COLLEGE PRIVE;COL;élèves;scolaires' 
	 WHEN '342' THEN 'GOD;COL;élèves;scolaires' 
	 WHEN '344' THEN 'CETAD;COL;élèves;scolaires' 
	 WHEN '350' THEN 'COLLEGE CLIMATIQUE;COL;élèves;scolaires' 
	 WHEN '380' THEN 'CTR FORMATION PROF. AGRICOLE;SUP;étudiants;d’étudiants' 
	 WHEN '430' THEN 'ECOLE DE FORMATION SANITAIRE ET SOCIALE;SUP;étudiants;d’étudiants' 
	 WHEN '523' THEN 'UNIVERSITE;SUP;étudiants;d’étudiants' 
	 WHEN '600' THEN 'CFA;SUP;étudiants;d’étudiants' 
	 WHEN '620' THEN 'ANNEXE DE CFA;SUP;étudiants;d’étudiants' 
	 WHEN 'ASS' THEN 'ASSOCIATION;SUP;étudiants;d’étudiants' 
	 WHEN 'BDE' THEN 'BUREAU DES ELEVES;SUP;étudiants;d’étudiants' 
	 WHEN 'BIB' THEN 'BIBLIOTHEQUE;SUP;étudiants;d’étudiants' 
	 WHEN 'ECO' THEN 'ECOLE;SUP;étudiants;d’étudiants' 
	 WHEN 'EXT' THEN 'EXTERNAT;SUP;étudiants;d’étudiants' 
	 WHEN 'INST' THEN 'INSTITUT;SUP;étudiants;d’étudiants' 
	 WHEN 'MAI' THEN 'MAIRIE;SUP;étudiants;d’étudiants' 
	 WHEN 'MLLE' THEN 'Mademoiselle;SUP;étudiants;d’étudiants' 
	 WHEN 'MME' THEN 'Madame;SUP;étudiants;d’étudiants' 
	 WHEN 'MR' THEN 'Monsieur;SUP;étudiants;d’étudiants' 
	 WHEN 'VID' THEN ';SUP;étudiants;d’étudiants' 
  ELSE '' END AS 'COMPNAME_PREFIX;DOMAIN;CUSTOM_CARTE_1;CUSTOM_CARTE_2' 
FROM CARTOFLEX.dbo.CLI CLI, CARTOFLEX.dbo.T2 T2 
WHERE CLI.DOS = T2.DOS AND T2.TIERS = CLI.TIERS AND CLI.DOS='1' AND CLI.REPR_0001 = 'ACS' AND CLI.EMAIL <> ''
UNION 
SELECT CLIENT.EMAILCONTACT AS 'EMAIL', CLI.TIERS AS 'TIERS', CLI.NOM AS 'COMPNAME', CLI.RUE AS 'ADDRESS_1', CLI.LOC AS 'ADDRESS_2', '' AS 'ADDRESS_3', CLI.CPOSTAL AS 'POSTCODE', CLI.VIL AS 'CITY', 
CLI.TEL AS 'PHONE', 
  CASE T2.TIT 
	 WHEN 'MR' THEN 'Monsieur' 
	 WHEN 'MME' THEN 'Madame' 
  ELSE '' END AS 'CIVILITY', 
T2.PRENOM AS 'SURNAME', T2.NOM AS 'NOM', '' AS 'CRYPTO', 'CARTE' AS 'SECTEUR', 'Agnès CORDA' AS 'COMMERCIAL', 'CLIENT' AS 'MATURITY', '' AS 'ZONE', '' AS 'ORDER_STATUS',
  CASE CLI.TIT 
	 WHEN '010' THEN 'CONSERVATOIRE;SUP;étudiants;d’étudiants' 
	 WHEN '020' THEN 'GROUPE SCOLAIRE;COL;élèves;scolaires' 
	 WHEN '030' THEN 'GRETA;SUP;étudiants;d’étudiants' 
	 WHEN '040' THEN 'IFSI;SUP;étudiants;d’étudiants' 
	 WHEN '154' THEN 'ECOLE PRIMAIRE;COL;élèves;scolaires' 
	 WHEN '300' THEN 'LEGT;COL;élèves;scolaires' 
	 WHEN '301' THEN 'LYCEE D ENSEIGNEMENT TECHNOLOGIQUE;COL;élèves;scolaires' 
	 WHEN '302' THEN 'LYCEE D ENSEIGNEMENT GENERAL;COL;élèves;scolaires' 
	 WHEN '303' THEN 'LYCEE D ENSEIGNEMENT GENERAL PRIVE;COL;élèves;scolaires' 
	 WHEN '306' THEN 'LYCEE POLYVALENT;COL;élèves;scolaires' 
	 WHEN '308' THEN 'LYCEE POLYVALENT PRIVE;COL;élèves;scolaires' 
	 WHEN '309' THEN 'LYCEE POLYVALENT DES METIERS;COL;élèves;scolaires' 
	 WHEN '310' THEN 'LYCEE CLIMATIQUE;COL;élèves;scolaires' 
	 WHEN '319' THEN 'LYCEE PROFESSIONNEL DES METIERS;COL;élèves;scolaires' 
	 WHEN '320' THEN 'LYCEE PROFESSIONNEL;COL;élèves;scolaires' 
	 WHEN '321' THEN 'LYCEE PROFESSIONNEL PRIVE;COL;élèves;scolaires' 
	 WHEN '322' THEN 'LYCEE;COL;élèves;scolaires' 
	 WHEN '340' THEN 'COLLEGE;COL;élèves;scolaires' 
	 WHEN '341' THEN 'COLLEGE PRIVE;COL;élèves;scolaires' 
	 WHEN '342' THEN 'GOD;COL;élèves;scolaires' 
	 WHEN '344' THEN 'CETAD;COL;élèves;scolaires' 
	 WHEN '350' THEN 'COLLEGE CLIMATIQUE;COL;élèves;scolaires' 
	 WHEN '380' THEN 'CTR FORMATION PROF. AGRICOLE;SUP;étudiants;d’étudiants' 
	 WHEN '430' THEN 'ECOLE DE FORMATION SANITAIRE ET SOCIALE;SUP;étudiants;d’étudiants' 
	 WHEN '523' THEN 'UNIVERSITE;SUP;étudiants;d’étudiants' 
	 WHEN '600' THEN 'CFA;SUP;étudiants;d’étudiants' 
	 WHEN '620' THEN 'ANNEXE DE CFA;SUP;étudiants;d’étudiants' 
	 WHEN 'ASS' THEN 'ASSOCIATION;SUP;étudiants;d’étudiants' 
	 WHEN 'BDE' THEN 'BUREAU DES ELEVES;SUP;étudiants;d’étudiants' 
	 WHEN 'BIB' THEN 'BIBLIOTHEQUE;SUP;étudiants;d’étudiants' 
	 WHEN 'ECO' THEN 'ECOLE;SUP;étudiants;d’étudiants' 
	 WHEN 'EXT' THEN 'EXTERNAT;SUP;étudiants;d’étudiants' 
	 WHEN 'INST' THEN 'INSTITUT;SUP;étudiants;d’étudiants' 
	 WHEN 'MAI' THEN 'MAIRIE;SUP;étudiants;d’étudiants' 
	 WHEN 'MLLE' THEN 'Mademoiselle;SUP;étudiants;d’étudiants' 
	 WHEN 'MME' THEN 'Madame;SUP;étudiants;d’étudiants' 
	 WHEN 'MR' THEN 'Monsieur;SUP;étudiants;d’étudiants' 
	 WHEN 'VID' THEN ';SUP;étudiants;d’étudiants' 
  ELSE '' END AS 'COMPNAME_PREFIX;DOMAIN;CUSTOM_CARTE_1;CUSTOM_CARTE_2' 
FROM CARTOFLEX.dbo.CLI CLI, CARTOFLEX.dbo.T2 T2, CARTOFLEX.dbo.CARTOFLEX_CONTACTS_CLIENT_V CLIENT 
WHERE CLI.DOS = T2.DOS 
AND CLIENT.TIERS = CLI.TIERS 
AND CLIENT.TIERS = T2.TIERS 
AND T2.TIERS = CLI.TIERS AND CLI.DOS='1' AND CLI.REPR_0001 = 'ACS' AND CLIENT.EMAILCONTACT <> ''
ORDER BY EMAIL ASC