Anybody has used a "if then else" statement in Oracle 9i Warehouse Builder? Have you find a workaround?
I guess it is possible to write a transformation or procedure ...
You do not mention what the actual problem is and why you need a work-around. In ORACLE the if then else can very effectively be substituted by the DECODE function:
I agree with blom0344 that DECODE is Oracle's "if" statement.
I also agree with xuewei that DECODE doesn't work in OWB as i have tried to use it.
I am trying to understand if OWB supports and how the "logic" that i am describing below.
Here is the situation:
<< if "CREATION_DATE" < "TODAY" then UPDATE else INSERT >>
I have a mapping where I want to update all records that were created before "today" and insert (as new) the records that were created "today" and onward...
I am guessing that a possible workaround would be to create my own procedure where I can write my own PL/SQL code (use of DECODE or CASE or ... ).
What I have already done is a similar mapping where I am having the fact table looking up against a "LOOKUP" stage, and OWB does update/insert genericly against the "LOOKUP" keys...
I have never tried to define a specific criteria for update/insert such as the "CREATION_DATE" and I am trying to understand if OWB supports this and how.
I am looking forward to hearing from anyone that has faced similar situations. Thanks!
I am really a bit of my own terrain here since I use Powermart (Informatica) as an ETL tool (not datawarehouse builder). In a Powermart mapping I can specify several 'branches', i.e. update,insert and delete branches. If I test for instance if a certain record has a date field older than the current date I direct it to the 'update' branche-part of the mapping. In Powermart terminology this is called 'data-driven' loading of a target.
Point is ,that if your target has a sensible primary key, this sort of stuff can be done just by a regular insert/update action. Records with a primary key that already exist in the target are updated, records with not-as-yet existing keys are inserted. I'm sure ORACLE warehouse builder must have this sort of functionality... T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com
I haven't tried it, or even tried to think it through completely, but I'm pretty sure you can do this without any transformation procedure, using filters.
You might be able to use 2 filters in one mapping. One filter would be creation_date < today and would do an update. The other creation_date >= today and would do an insert.
If nothing else, you can definitely do that using 2 mappings.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.