1 Reply Latest reply on Nov 18, 2010 11:03 AM by Ramesh Reddy

    Regarding multiple left outer join in Teiid Designer

    jalen zhong Newbie

      I have a table which needs to left join other 3 tables.


      The script looks like:

      select ...

        ((CIS_Artesia.Artesia.towner.TC_ASSET AS ta LEFT OUTER JOIN CIS_Artesia.Artesia.towner.PROJECT_ASSIGNEES AS pa ON ta.UOI_ID = pa.UOI_ID) LEFT OUTER JOIN CIS_Artesia.Artesia.towner.UOI_RIGHTS AS ur ON ur.UOI_ID = ta.UOI_ID) LEFT OUTER JOIN CIS_Artesia.Artesia.towner.IPTC_APP_RECORDS AS iar ON iar.UOI_ID = ta.UOI_ID


      Designer prompts errors: "Symbol ta.CIS_Artesia.Artesia.towner.TC_ASSET.UOI_ID is specified with an unknown group context".


      It can work if removing the alias for table:

      select ...

        ((CIS_Artesia.Artesia.towner.TC_ASSET LEFT OUTER JOIN CIS_Artesia.Artesia.towner.PROJECT_ASSIGNEES ON CIS_Artesia.Artesia.towner.TC_ASSET.UOI_ID = CIS_Artesia.Artesia.towner.PROJECT_ASSIGNEES.UOI_ID) LEFT OUTER JOIN CIS_Artesia.Artesia.towner.UOI_RIGHTS ON CIS_Artesia.Artesia.towner.UOI_RIGHTS.UOI_ID = CIS_Artesia.Artesia.towner.TC_ASSET.UOI_ID) LEFT OUTER JOIN CIS_Artesia.Artesia.towner.IPTC_APP_RECORDS ON CIS_Artesia.Artesia.towner.IPTC_APP_RECORDS.UOI_ID = CIS_Artesia.Artesia.towner.TC_ASSET.UOI_ID



      Here we remove all the "AS ta, As ur" scripts, then Designer works well. But it is really complex for us if alias does not work well for multiple outer join. Could you please help me to solove this problem?


      Thanks a lot!