4 Replies Latest reply: Sep 18, 2017 11:18 AM by Andrea Gigliotti RSS

    Linking Direct Discovery table twice

    Andrea Gigliotti

      Hello,

      I'd need to write an sql statement for direct query (DB2/400).

      Specifically I need to join the same table twice using different where clause something similar below:

       

      DIRECT QUERY

      DIMENSION

      "ORDNUM" as "AliasColumn1",

      "ORDNRG" as "AliasColumn2",

      "ORDAGO" as "AliasColumn3"

       

      MEASURE

      ORDNUM,

      ORDIFT,

      ORDCRD

       

      FROM "library1"."table"

      JOIN "library2"."table1"

      ON (SUBSTR("library2"."table1"."ATBKEY", 14, 2) ="library1"."table"."ORDTIP" and "library2"."table1"."ATBCOD" = 'C21')

      JOIN "library2"."table1"

      ON (SUBSTR("library2"."table1"."ATBKEY", 14, 2) ="library1"."table"."ORDTPR" and "library2"."table1"."ATBCOD" = 'C58')

      ...

       

      but It give me an error for "library2"."table1" duplicated !


      I read on direct discovery documentation as below:

      It is not possible to refer to tables by alias in the FROM clause, you need to use the full table name.


      Can someone help me with this ?


      Many thanks in advance.


      Best Regards

      Andrea