1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
| SELECT
"CLIENT"."Code" AS "Info EBP",
"CLIENT"."CODE" AS "Code Tiers",
UPPER(SUBSTRING("CLIENT"."RaisonSociale" , 1 , 20)) AS "Nom Reduit",
IF("CLIENT"."LastFacture_Date" = NULL() , "CLIENT"."OuvertLe" , "CLIENT"."LastFacture_Date" ) "Date Creation",
"CLIENT"."Compte" AS "Compte Auxiliaire",
"CLIENT"."NumTVA" AS "Numero TVA",
"CLIENT"."Siret" AS "Siret",
SUBSTRING("CLIENT"."Siret",1,9) AS "Siren",
IF ( "CLIENT"."Contact10_Email" LIKE '%@%' , "CLIENT"."Contact10_Email" ,
IF ( "CLIENT"."Contact11_Email" LIKE '%@%' , "CLIENT"."Contact11_Email" ,
IF ( "CLIENT"."Contact20_Email" LIKE '%@%' , "CLIENT"."Contact20_Email" ,
IF ( "CLIENT"."Contact21_Email" LIKE '%@%' , "CLIENT"."Contact21_Email" ,
IF ( "CLIENT"."Adresse1_Adresse" LIKE '%@%' , "CLIENT"."Adresse1_Adresse" ,
IF ( "CLIENT"."Contact10_Nom" LIKE '%@%' , "CLIENT"."Contact10_Nom" ,
IF ( "CLIENT"."Contact11_Nom" LIKE '%@%' , "CLIENT"."Contact11_Nom" ,
'' ) ) ) ) ) ) ) "Email",
IF ( "CLIENT"."Adresse1_Adresse" LIKE '%www%' , "CLIENT"."Adresse1_Adresse" ,
IF ( "CLIENT"."Adresse1_Ville" LIKE '%www%' , "CLIENT"."Adresse1_Ville" ,
IF ( "CLIENT"."Contact10_URL" LIKE '%www%' , "CLIENT"."Contact10_URL" ,
IF ( "CLIENT"."Contact11_URL" LIKE '%www%' , "CLIENT"."Contact11_URL" ,
'' ) ) ) ) "Site Web",
'2015' AS "Tarif",
"MODEREGL"."Reglement" AS "Condition Reglement",
'' AS "Activite",
'' AS "Type",
'' AS "Nombre BL",
NB
FROM
"CLIENT"
LEFT OUTER JOIN "MODEREGL"
ON "CLIENT"."Reglement" = "MODEREGL"."Code"
INNER JOIN
( SELECT "CLIENT"."Code" as B_CODE,
COUNT(*) NB
FROM "FACTURE",
"CLIENT"
WHERE "FACTURE"."Client" = "CLIENT"."CODE"
AND "FACTURE"."Facture" <> 0
GROUP BY "CLIENT"."Code"
) B on B_CODE = "CLIENT"."Code"
GROUP BY "CLIENT"."Code" |
Partager