Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Error "Table/Alias non unique" in PME

  1. #1

    Default Error "Table/Alias non unique" in PME

    Hi !

    I'm working with Pentaho Metadata Editor and I have a problem with the SQL which is generated by the query editor.

    In the SQL, a same table is referenced in 2 joins. It's the table named "fact". And it causes an error :
    An error occurred executing SQL:
    SELECT DISTINCT
    BT_DOSSIER_DOSSIER.index_dossier AS COL0
    ,BT_PRESENCE_RSA_PRESENCE_RSA.num_demande_rsa AS COL1
    ,BT_INDIVIDU_INDIVIDU.index_individu AS COL2
    ,BT_INDIVIDU_INDIVIDU.genre_individu AS COL3
    ,BT_INDIVIDU_INDIVIDU.nom_individu AS COL4
    ,BT_INDIVIDU_INDIVIDU.prenom_individu AS COL5
    ,BT_INDIVIDU_INDIVIDU_CONJOINT.index_individu AS COL6
    ,BT_INDIVIDU_INDIVIDU_CONJOINT.genre_individu AS COL7
    ,BT_INDIVIDU_INDIVIDU_CONJOINT.nom_individu AS COL8
    ,BT_INDIVIDU_INDIVIDU_CONJOINT.prenom_individu AS COL9
    ,BT_DOSSIER_DOSSIER.numero_lieu AS COL10
    ,BT_NATURE_LIEU_NATURE_LIEU.lc_nature_lieu AS COL11
    ,BT_LIEU_LIEU.nom_lieu AS COL12
    ,BT_COMMUNE_COMMUNE.nom_com AS COL13
    ,BT_CANTON_CANTON.lc_canton AS COL14
    ,BT_CIRCONSCRIPTION_CIRCONSCRIPTION.lc_circonscription AS COL15
    ,BT_CLI_CLI.lc_cli AS COL16

    FROM individu BT_INDIVIDU_INDIVIDU_CONJOINT JOIN
    (
    fact BT_FACT_FACT JOIN
    (
    dossier BT_DOSSIER_DOSSIER JOIN
    (
    fact BT_FACT_FACT JOIN
    (
    individu BT_INDIVIDU_INDIVIDU JOIN
    (
    canton BT_CANTON_CANTON JOIN
    (
    circonscription BT_CIRCONSCRIPTION_CIRCONSCRIPTION JOIN
    (
    cli BT_CLI_CLI JOIN
    (
    commune BT_COMMUNE_COMMUNE JOIN
    (
    lieu BT_LIEU_LIEU LEFT OUTER JOIN nature_lieu BT_NATURE_LIEU_NATURE_LIEU
    ON ( BT_LIEU_LIEU.code_nature_lieu = BT_NATURE_LIEU_NATURE_LIEU.code_nature_lieu )
    )
    ON ( ( BT_COMMUNE_COMMUNE.code_a_com = BT_LIEU_LIEU.code_a_com ) AND ( BT_COMMUNE_COMMUNE.code_dep = BT_LIEU_LIEU.code_dep ) AND ( BT_COMMUNE_COMMUNE.nom_com = 'AVRILLE' ) )
    )
    ON ( BT_CLI_CLI.code_cli = BT_LIEU_LIEU.code_cli )
    )
    ON ( BT_CIRCONSCRIPTION_CIRCONSCRIPTION.code_circonscription = BT_LIEU_LIEU.code_circonscription )
    )
    ON ( ( BT_CANTON_CANTON.code_dep = BT_LIEU_LIEU.code_dep ) AND ( BT_CANTON_CANTON.code_canton = BT_LIEU_LIEU.code_canton ) )
    )
    ON ( BT_INDIVIDU_INDIVIDU.index_individu = BT_FACT_FACT.index_allocataire )
    )
    ON ( ( BT_PRESENCE_RSA_PRESENCE_RSA.index_menage_rsa = BT_FACT_FACT.index_menage_rsa ) AND ( BT_PRESENCE_RSA_PRESENCE_RSA.index_presence_rsa = BT_FACT_FACT.index_presence_rsa ) )
    )
    ON ( BT_DOSSIER_DOSSIER.index_dossier = BT_FACT_FACT.index_dossier )
    )
    ON ( ( BT_LIEU_LIEU.code_dep = BT_FACT_FACT.code_dep ) AND ( BT_LIEU_LIEU.code_a_com = BT_FACT_FACT.code_a_com ) AND ( BT_LIEU_LIEU.code_lieu = BT_FACT_FACT.code_lieu ) )
    )
    ON ( BT_INDIVIDU_INDIVIDU_CONJOINT.index_individu = BT_FACT_FACT.index_conjoint )


    Table/alias: 'BT_FACT_FACT' non unique
    I don't understand why there is two references of my table "fact"... Can you help me please ?

    Thank you.
    Last edited by Borowsky; 03-05-2010 at 07:53 AM. Reason: wrong title

  2. #2

    Default

    For the moment, the problem is solved...

    I don't know really how... I think we have to play with the field "" of each join putting letters in it, to choise the order of execution of joins...

  3. #3

    Default

    Hi,

    You are using outer joins, so you're correct that the join order is important.
    Have a look at Matt's post over here: http://www.ibridge.be/?p=99

    Cheers,

    Bart

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.