"if .. then .. else" in Oracle Warehouse (possible workarounds)

[Loukas]

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 ...

Thanks a lot for your answers...

 

[blom0344]

Hello Loukas,

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:

DECODE(expr,value1[,return1,value2,return2.....,]default)

Is this the kind of function you are searching for? T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[xuewei]

Hi,


But DECODE will not work at OWB. You can use CASE instead.


Regards

Xuewei

 

[Loukas]

Thank you blom0344 and xuewei for your answers.

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 &quot;CREATION_DATE&quot; < &quot;TODAY&quot; then UPDATE else INSERT >>

I have a mapping where I want to update all records that were created before &quot;today&quot; and insert (as new) the records that were created &quot;today&quot; 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 &quot;LOOKUP&quot; stage, and OWB does update/insert genericly against the &quot;LOOKUP&quot; keys...

I have never tried to define a specific criteria for update/insert such as the &quot;CREATION_DATE&quot; 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!

 

[blom0344]

Hello Loukas,

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

 

[BSeefelt]

Hi,

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.

Hope that made sense and helped,

BSeefelt