Need Suggesion for the below logic

[Former Member]

Hi Experts,

i have a 6 fields a,b,c,d,e,f in the source and in the target i have one field k

My mapping requirement is

if a =1 then K =b

suppose if b is blank

then k=c

suppose if c is blank

then k = d

suppose if d is blank

then k = e

suppose if e is blank

then k=f

In the mapping i wrote a decode function as below

decode ( a = '1',b,

              (a='1' and b = ' '),c,

             (a='1' and c = ' '),d,

            (a='1' and d = ' '),e,

            (a='1' and e = ' '),f,null)

My problem is when the first field i.e if b is blank, i should get c values, i am using 4.1 version, i am not getting expected

Any Suggesion please

Thanks

Madhu

[Former Member · Accepted solution]

Write a ifthenelse statement, i guess the below statement should work.

ifthenelse(a=1,b,

     ifthenelse(b="" or

       ifthenelse(c="" or

              ifthenelse(d=0 or

                ifthenelse(e=0,f,e),c),d),e))

[Former Member · Accepted solution]

The logic did not Work

Any other suggesion

Thanks

Madhu

[Former Member · Accepted solution]

Whats the error you are getting?

[Former Member · Accepted solution]

i am not getting result as expected the below is the code i am using

ifthenelse( T_APPEAL_PARTY_LOAD_RESULTSET1.ADDR_CODE ='0',LR_BVS_COMCD1.CDF_CD1_CITY,

ifthenelse( T_APPEAL_PARTY_LOAD_RESULTSET1.ADDR_CODE !='0',T_APPEAL_PARTY_LOAD_RESULTSET1.ADDRESS_5,

  ifthenelse( (T_APPEAL_PARTY_LOAD_RESULTSET1.ADDR_CODE !='0' and T_APPEAL_PARTY_LOAD_RESULTSET1.ADDRESS_5 =''),T_APPEAL_PARTY_LOAD_RESULTSET1.ADDRESS_4,

       ifthenelse( (T_APPEAL_PARTY_LOAD_RESULTSET1.ADDR_CODE !='0' and T_APPEAL_PARTY_LOAD_RESULTSET1.ADDRESS_5 ='' and T_APPEAL_PARTY_LOAD_RESULTSET1.ADDRESS_4 =''),T_APPEAL_PARTY_LOAD_RESULTSET1.ADDRESS_3,

         ifthenelse( (T_APPEAL_PARTY_LOAD_RESULTSET1.ADDR_CODE !='0' and T_APPEAL_PARTY_LOAD_RESULTSET1.ADDRESS_5 ='' and T_APPEAL_PARTY_LOAD_RESULTSET1.ADDRESS_4 ='' and T_APPEAL_PARTY_LOAD_RESULTSET1.ADDRESS_3 =''),T_APPEAL_PARTY_LOAD_RESULTSET1.ADDRESS_2,

                 ifthenelse( (T_APPEAL_PARTY_LOAD_RESULTSET1.ADDR_CODE !='0' and T_APPEAL_PARTY_LOAD_RESULTSET1.ADDRESS_5 ='' and T_APPEAL_PARTY_LOAD_RESULTSET1.ADDRESS_4 ='' and T_APPEAL_PARTY_LOAD_RESULTSET1.ADDRESS_3 =''and T_APPEAL_PARTY_LOAD_RESULTSET1.ADDRESS_2 =''),T_APPEAL_PARTY_LOAD_RESULTSET1.ADDRESS_1,NULL ))))))

[Former Member · Accepted solution]

OK.

Couple of questions -

Whats the datatype of each column a,b,c,d,e,f?

What if some are blank and some are not? Say if a=1 and b=1?

Also are they always 0 and blank or 1 and blank? Or any number can be in a, b,c,d,e,f

[Former Member · Accepted solution]

The Data types A is int

B,C,D,E,F are varchar (30)

K is varchar(40)

The requirement is

if A =1 then K =B

suppose if B is blank

then K=C

suppose if C is blank

then K = D

suppose if D is blank

then K = E

suppose if E is blank

then K=F

Out of 5  fields--B,C,D,E,F . K value should be the first value out of these 5 fields in the Order B ,C, D, E, F which is not blank

Please let me know if you did not understand

Thanks

Madhu

[former_member186897 · Accepted solution]

use NVL() while comparing it for the blank. Example: nvl(b,'')='' 

[Former Member · Accepted solution]

Ok.

So your transformation is searching for addresses with same address code. Correct?

In that case, its easier to concatenate b,c,d,e and f corresponding to the address code in a.

Like this

b||'-'||c||'-'||d||'-'||e||'-'||f||'-'||

To get the first occurance use word_ext function

word_ext(concatenatedcolname,1,'-')

This should solve the issue.

Arun

[Former Member · Accepted solution]

Thanks Arun it worked

[Former Member · Accepted solution]

Hi,

If the issue is solved, reward with appropriate points and close the post.

Arun

[Former Member]

Thanks everyone

[former_member205887]

Make sure you are getting blank space(Single Space) from Source, looks like you are getting NULL value or more than one space ....

Code accordingly either using trim or NVL wrt your data and check....