re, j'ai extrait les données qu'il faut pour les exports de captures par chalut du site des indicateurs, mais je me galère pour les mettre en forme en SQL comme dans les fichiers de sortie du site des indicateurs. Peux tu arranger ça? ci-dessous les requêtes pour les captures par chalut à exporter du site des indicateurs, avec des trucs à faire en plus que je ne sais pas faire en SQL. 1. Fichier "reftax_Species" : SELECT "taxoncode" AS "C_Perm", "taxonsystematicorder" AS "NumSys", "taxonsystematiclevel" AS "NivSys", "baracoudacode" AS "C_VALIDE", "genusspecies" AS "L_VALIDE", "authorreference" AS "AA_VALIDE", "taxonfathermemocode" AS "C_TxPère" FROM "public"."species" AS "species" dans cette requête, il faut en plus : - enlever le '_' dans le baracoudacode Il n'y a pas de colonne "taxa" 2. Fichier "capture" SELECT "voyage"."name" AS "Campagne", "species"."baracoudacode" AS "Espece", "operation"."id" AS "Trait", "sample"."numbersampled" AS "Nombre", "sample"."sampleweight" AS "Poids" FROM "public"."transit" AS "transit", "public"."voyage" AS "voyage", "public"."transect" AS "transect", "public"."operation" AS "operation", "public"."sample" AS "sample", "public"."speciescategory" AS "speciescategory", "public"."species" AS "species" WHERE "transit"."voyage" = "voyage"."topiaid" AND "transect"."transit" = "transit"."topiaid" AND "operation"."transect" = "transect"."topiaid" AND "sample"."operation" = "operation"."topiaid" AND "sample"."speciescategory" = "speciescategory"."topiaid" AND "speciescategory"."species" = "species"."topiaid" dans cette requête, il faut en plus : - enlever le '_' dans le baracoudacode - créer un champ "Annee" avec l'année du voyage - faire une somme des 'Nombre" et "Poids" en groupant sur les champs "Campagne","trait", "Espece" et "Annee", mais j'y arrive pas... 3. Fichier "tailles" SELECT "voyage"."name" AS "Campagne", "operation"."id" AS "Trait", "species"."baracoudacode" AS "Espece", "sexcategory"."name" AS "Sexe", "sampledata"."datalabel", "sampledata"."datavalue", "sampledatatype"."name" FROM "public"."voyage" AS "voyage", "public"."mission" AS "mission", "public"."transit" AS "transit", "public"."transect" AS "transect", "public"."operation" AS "operation", "public"."sample" AS "sample", "public"."sampledata" AS "sampledata", "public"."sampledatatype" AS "sampledatatype", "public"."sampletype" AS "sampletype", "public"."speciescategory" AS "speciescategory", "public"."species" AS "species", "public"."sizecategory" AS "sizecategory", "public"."sexcategory" AS "sexcategory" WHERE "voyage"."mission" = "mission"."topiaid" AND "transit"."voyage" = "voyage"."topiaid" AND "transect"."transit" = "transit"."topiaid" AND "operation"."transect" = "transect"."topiaid" AND "sample"."operation" = "operation"."topiaid" AND "sampledata"."sample" = "sample"."topiaid" AND "sampledata"."sampledatatype" = "sampledatatype"."topiaid" AND "sample"."sampletype" = "sampletype"."topiaid" AND "sample"."speciescategory" = "speciescategory"."topiaid" AND "speciescategory"."species" = "species"."topiaid" AND "speciescategory"."sizecategory" = "sizecategory"."topiaid" AND "speciescategory"."sexcategory" = "sexcategory"."topiaid" AND "sampletype"."name" = 'Subsample' dans cette requête, il faut en plus : - enlever le '_' dans le baracoudacode - créer un champ "Annee" avec l'année du voyage - mettre en colonne les champs "LTcm1" et "WeightAtLengthkg" - faire une somme des datavalues des 'LTcm1" et "WeightAtLengthkg" respectivement en groupant sur les champs "Campagne","trait", "Espece" et "Annee", mais j'y arrive pas... - ajouter une colonne "maturite" avec des NA - convertir les "N" de la colonne "sexe" en NA 4. Fichier "strates" SELECT "voyage"."name" AS "Campagne", "cell"."name" AS "Strate", "datametadata"."name", "data"."datavalue" AS "Surface" FROM "public"."voyage" AS "voyage", "public"."mission" AS "mission", "public"."cell" AS "cell", "public"."celltype" AS "celltype", "public"."data" AS "data", "public"."datametadata" AS "datametadata" WHERE "voyage"."mission" = "mission"."topiaid" AND "cell"."voyage" = "voyage"."topiaid" AND "cell"."celltype" = "celltype"."topiaid" AND "data"."cell" = "cell"."topiaid" AND "data"."datametadata" = "datametadata"."topiaid" AND "celltype"."id" = 'Region' AND "datametadata"."name" = 'Surface' là c'est bon 5. Fichier "traits" j'ai pas fini, c'est plus compliqué, faut qu'on en cause... M -- Mathieu Doray, PhD Ifremer Département Ecologie et Modèles pour l'Halieutique rue de l'Ile d'Yeu B.P. 21105 44311 Nantes Cedex 03 mathieu.doray@ifremer.fr Tel./Phone: 02 40 37 41 65 / International: 332 40 37 41 65 Fax : 02.40.37.40.01 / International: 332 40 37 40 01 CV / resume: http://annuaire.ifremer.fr/cv/17093/