This content has been marked as final. Show 3 replies
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)) !=
from (select substr(orders.DISCOUNT_CODES, rPos, 1) rDC
from (select level rPos
connect by level <11
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?
Edited by: Cezar Santos - www.odiexperts.com on 29/09/2009 19:22
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 ?
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?