5 Replies Latest reply: Jan 17, 2013 9:57 AM by JeromeFr RSS

    ODI - Use distinct inside look up field

    973458
      Hi everybody,
      in my project i've an interface with 1 source table and 1 lookup, which type is "sql expression in the select clause".
      Here is the problem:
      how can I set the distinct ONLY in the subquery? If I write "distinct" before the mapped field, ODI put it outside the subquery, so the code is invalid
      I.E.
      I'd like something like this:

      SELECT a1,
      a2,
      a3,
      (SELECT DISTINCT a4
      FROM lookup where lookup.field1=source.field1)
      FROM SOURCE

      but ODI do this:

      SELECT a1,
      a2,
      a3,
      DISTINCT (SELECT a4
      FROM lookup where lookup.field1=source.field1)
      FROM SOURCE


      How can I achieve my goal without using the distinct clause in the work flow?

      Thanks in advance