3 Replies Latest reply: Oct 1, 2009 10:15 AM by CezarSantos-http://odiexperts.com RSS

    Multi table constraint or using a complex condition for a constraint

    A J
      Something went wrong with this post
        • 1. Re: Multi table constraint or using a complex condition for a constraint
          CezarSantos-http://odiexperts.com
          Hi,

          Let me try to help...

          You don't need to have the FK defiined in the database to use it at ODI, at true, with this feature, you can even define a FK between technologies (for instance, file to oracle).

          In your case, if I understood right, as there are concatenated values into the child column, you could use a Condition, in the Orders table, like:



          length(replace(orders.DISCOUNT_CODES, ' ',null)) !=
          (select count(*)
          from (select substr(orders.DISCOUNT_CODES, rPos, 1) rDC
          from (select level rPos
          from dual
          connect by level <11
          )
          ) tab_DC,
          Discount
          where tab_DC.rDC = discount.DISCOUNT_CODE
          )

          In this way, the amout of code in the orders.DISCOUNT_CODES must be the same of a count from the union of each individual code wiht the DISCOUNT TABLE.

          Please try it, I just write withou test once I didn't create your tables here.

          Does it work for you?


          Cezar Santos
          [www.odiexperts.com]

          Edited by: Cezar Santos - www.odiexperts.com on 29/09/2009 19:22
          • 2. Re: Multi table constraint or using a complex condition for a constraint
            A J
            Thanks Cezar,

            I would certainly try this approach. I was not sure earlier if I can reference a second Data Store's column in the constraint of columns on first datastore.

            For the other 2 questions that are still unanswered, should I create new posts in the ODI forum or can they be answered here ?

            Thanks
            • 3. Re: Multi table constraint or using a complex condition for a constraint
              CezarSantos-http://odiexperts.com
              Hi,

              Sorry, I was concentrated to solve the first one and forgot the others..

              Here are my considerations:


              2) This needs more information to find a solution because, normally, constraints are dependent from column physical name. If they have the same column name at distinct tables then ODI function could be used to achieve this.


              3) Yes, it is possible to use pl/sql but not as anonymous block. A possible approach is create an ODI procedure that create a pl/sql function and then uses this pl/sql function in the constraint.
              This pl/sql function can be dynamically created based in table structure (for instance) thru the use of JKM for table related code. For that KM customizing will be necessary.

              Make any sense?

              Cezar Santos
              [www.odiexperts.co]