ifthenelse and lookup

dataservices job design
1

Hi,

I am using the ifthenelse and lookup to achieve the following implementation but get stuck after step 2 below.

Please advise.

Lookup Table :
REF_TABLE1 as R1
Condition:
SRC.SRC_CENTRE =R1.OLD_CENTRE
Output: R1.NEW_CENTRE

If R1.NEW_CENTRE is not null
Then
Step 2
R1.NEW_CENTRE
Else
Step 2
If TGT.STATUS_GROUP in
(Select STATUS_GROUP From REF_TABLE2 Where OLD_Centre is not null )
Then
Lookup Table :
REF_TABLE3 as R3
Condition:
R2.NEW_CODE= CODE_TEMP
Output: R2.SUS_CENTRE_TRANS||’-Z’
Else
Output: NULL

its_ranjan (BOB member since 2011-02-16)

2

The most direct approach would be using three queries, the first does the first condition, based on its output you do the second lookup in the second query etc.

Werner Daehn :de: (BOB member since 2004-12-17)

3

Hi Werner,

I just want to manage in a single query :wink:

The code is:-

ifthenelse(REF_TABLE1.NEW_CENTRE is not null, REF_TABLE1.NEW_CENTRE, ifthenelse(sql('DS', 'SELECT  STATUS_GROUP FROM REF_TABLE2 WHERE OLD_CENTRE is not null ') ='SOMETHING', REF_TABLE3.SUS_CENTRE_TRANS||'-Z', 'NULL'))

The issue is the following seems to be not fitting good in the above.

its_ranjan (BOB member since 2011-02-16)

4

Never use the sql() function in dataflows!

See the first table in this link
http://wiki.sdn.sap.com/wiki/display/EIM/How+to+lookup+a+row

So you replace the test

sql(‘DS’, 'SELECT STATUS_GROUP FROM REF_TABLE2 WHERE OLD_CENTRE is not null ')

with a lookup_ext() function.

But frankly I don’t understand that select statement, I mean what the use is. It will return the same value always, the first row with OLD_CENTRE not null.

Werner Daehn :de: (BOB member since 2004-12-17)

5

Hi Werner,

The main issue is to implement this piece of lookup in the mapping which does not suit well :-

if STATUS_GROUP value return from that SELECT from REF_TABLE2 is present in STATUS_GROUP of TGT table.

The pseudocode then :-

ifthenelse(REF_TABLE1.NEW_CENTRE is not null, REF_TABLE1.NEW_CENTRE, ifthenelse(STATUS_GROUP FROM REF_TABLE2 WHERE OLD_CENTRE is not null ') IS PPRESENT IN TGT.STATUS_GROUP , REF_TABLE3.SUS_CENTRE_TRANS||'-Z', 'NULL'))

its_ranjan (BOB member since 2011-02-16)

6

It seems to be a tough nut to crack.
Would SQL transfom be helpful? I can think of splitting in three queries as Werner suggested, but need more inputs.

its_ranjan (BOB member since 2011-02-16)

7

Will not be helpfull. You are looking for a technical solution while you do not u derstand the functional spec or fail to implement it with the right logic.

Johannes Vink :netherlands: (BOB member since 2012-03-20)

8

The functional requirement is clear from the very first post.
The implementation here seems to be difficult

its_ranjan (BOB member since 2011-02-16)

9

Any luck for this? :sleeping:

its_ranjan (BOB member since 2011-02-16)

10

This statement does not serve you well:

You may understand the requirement but if you have not provided sufficient information in your posting then we don’t understand it and are unlikely to arrive at a solution that satisfies the requirement. Keep in mind that we are volunteers here. A combative forum member with an exalted attitude will likely be ignored by those of us that can help.

Can you write a query that satisfies your requirement that can be executed directly against the Oracle database using a tool such as SQL*Plus? If you can construct that then we may be able to tell you how to write it using native Dataflow objects. Then again, if you can write that query then the path of least resistance is to simply paste the query into a SQL transform and be done with it.

eganjp :us: (BOB member since 2007-09-12)

11

Thanks Jim :slight_smile:

its_ranjan (BOB member since 2011-02-16)