On Fri, 06 Apr 2012 14:29:36 +0200 Mathieu DORAY <Mathieu.Doray@ifremer.fr> wrote:
1er pb dans les requêtes (lié au format SQL Echobase je crois, et aux multiples "names" qui trainent dans la base) :
- j'ai essayé de faire une requête qui extrait tous les fishing samples d'Echobase dans LibO. ça marche bien avec :
SELECT "VOYAGE"."NAME", "OPERATION"."ID", "SAMPLETYPE"."NAME", "SPECIES"."GENUSSPECIES", "SIZECATEGORY"."NAME", "SAMPLE"."SAMPLEWEIGHT", "SAMPLE"."NUMBERSAMPLED", "SAMPLEDATATYPE"."NAME", "SAMPLEDATA"."DATALABEL", "SAMPLEDATA"."DATAVALUE" FROM "ECHOBASE"."PUBLIC"."VOYAGE" AS "VOYAGE", "ECHOBASE"."PUBLIC"."MISSION" AS "MISSION", "ECHOBASE"."PUBLIC"."TRANSIT" AS "TRANSIT", "ECHOBASE"."PUBLIC"."TRANSECT" AS "TRANSECT", "ECHOBASE"."PUBLIC"."OPERATION" AS "OPERATION", "ECHOBASE"."PUBLIC"."OPERATIONMETADATAVALUE" AS "OPERATIONMETADATAVALUE", "ECHOBASE"."PUBLIC"."OPERATIONMETADATA" AS "OPERATIONMETADATA", "ECHOBASE"."PUBLIC"."SAMPLE" AS "SAMPLE", "ECHOBASE"."PUBLIC"."SAMPLETYPE" AS "SAMPLETYPE", "ECHOBASE"."PUBLIC"."SPECIESCATEGORY" AS "SPECIESCATEGORY", "ECHOBASE"."PUBLIC"."SPECIES" AS "SPECIES", "ECHOBASE"."PUBLIC"."SIZECATEGORY" AS "SIZECATEGORY", "ECHOBASE"."PUBLIC"."SAMPLEDATA" AS "SAMPLEDATA", "ECHOBASE"."PUBLIC"."SAMPLEDATATYPE" AS "SAMPLEDATATYPE" WHERE "VOYAGE"."MISSION" = "MISSION"."TOPIAID" AND "TRANSIT"."VOYAGE" = "VOYAGE"."TOPIAID" AND "TRANSECT"."TRANSIT" = "TRANSIT"."TOPIAID" AND "OPERATION"."TRANSECT" = "TRANSECT"."TOPIAID" AND "OPERATIONMETADATAVALUE"."OPERATION" = "OPERATION"."TOPIAID" AND "OPERATIONMETADATAVALUE"."OPERATIONMETADATA" = "OPERATIONMETADATA"."TOPIAID" AND "SAMPLE"."OPERATION" = "OPERATION"."TOPIAID" AND "SAMPLE"."SAMPLETYPE" = "SAMPLETYPE"."TOPIAID" AND "SAMPLE"."SPECIESCATEGORY" = "SPECIESCATEGORY"."TOPIAID" AND "SPECIESCATEGORY"."SPECIES" = "SPECIES"."TOPIAID" AND "SPECIESCATEGORY"."SIZECATEGORY" = "SIZECATEGORY"."TOPIAID" AND "SAMPLEDATA"."SAMPLE" = "SAMPLE"."TOPIAID" AND "SAMPLEDATA"."SAMPLEDATATYPE" = "SAMPLEDATATYPE"."TOPIAID"
- quand je la traduis dans echobase, ça donne :
SELECT VOYAGE.NAME, OPERATION.ID, SAMPLETYPE.NAME, SPECIES.GENUSSPECIES, SIZECATEGORY.NAME, SAMPLE.SAMPLEWEIGHT, SAMPLE.NUMBERSAMPLED, SAMPLEDATATYPE.NAME, SAMPLEDATA.DATALABEL, SAMPLEDATA.DATAVALUE FROM ECHOBASE.PUBLIC.VOYAGE AS VOYAGE, ECHOBASE.PUBLIC.MISSION AS MISSION, ECHOBASE.PUBLIC.TRANSIT AS TRANSIT, ECHOBASE.PUBLIC.TRANSECT AS TRANSECT, ECHOBASE.PUBLIC.OPERATION AS OPERATION, ECHOBASE.PUBLIC.OPERATIONMETADATAVALUE AS OPERATIONMETADATAVALUE, ECHOBASE.PUBLIC.OPERATIONMETADATA AS OPERATIONMETADATA, ECHOBASE.PUBLIC.SAMPLE AS SAMPLE, ECHOBASE.PUBLIC.SAMPLETYPE AS SAMPLETYPE, ECHOBASE.PUBLIC.SPECIESCATEGORY AS SPECIESCATEGORY, ECHOBASE.PUBLIC.SPECIES AS SPECIES, ECHOBASE.PUBLIC.SIZECATEGORY AS SIZECATEGORY, ECHOBASE.PUBLIC.SAMPLEDATA AS SAMPLEDATA, ECHOBASE.PUBLIC.SAMPLEDATATYPE AS SAMPLEDATATYPE WHERE VOYAGE.MISSION = MISSION.TOPIAID AND TRANSIT.VOYAGE = VOYAGE.TOPIAID AND TRANSECT.TRANSIT = TRANSIT.TOPIAID AND OPERATION.TRANSECT = TRANSECT.TOPIAID AND OPERATIONMETADATAVALUE.OPERATION = OPERATION.TOPIAID AND OPERATIONMETADATAVALUE.OPERATIONMETADATA = OPERATIONMETADATA.TOPIAID AND SAMPLE.OPERATION = OPERATION.TOPIAID AND SAMPLE.SAMPLETYPE = SAMPLETYPE.TOPIAID AND SAMPLE.SPECIESCATEGORY = SPECIESCATEGORY.TOPIAID AND SPECIESCATEGORY.SPECIES = SPECIES.TOPIAID AND SPECIESCATEGORY.SIZECATEGORY = SIZECATEGORY.TOPIAID AND SAMPLEDATA.SAMPLE = SAMPLE.TOPIAID AND SAMPLEDATA.SAMPLEDATATYPE = SAMPLEDATATYPE.TOPIAID
et quand je l'exécute (cf sur demo), j'obtiens 2 colonnes avec les données de "SAMPLEDATATYPE"."NAME" (en dialecte LibO) : une pour la donnée "SAMPLEDATATYPE"."NAME", ce qui est bien, mais aussi une pour la colonne "SIZECATEGORY"."NAME", ce qui est moins bien... (car je voudrais vraiment afficher "SIZECATEGORY"."NAME" et non pas "SAMPLEDATATYPE"."NAME"...
???
Bah il suffit de supprimer le SAMPLEDATATYPE"."NAME" du select non ? Je ne sais pas du tout comment fonctionner le générateur de requète de libreO, moi juste connaitre le sql. Mais au final en relisant ta question je suis pas sur de la comprendre... :( -- Tony Chemit -------------------- tél: +33 (0) 2 40 50 29 28 email: chemit@codelutin.com http://www.codelutin.com