square brackets as a text in DB2 environment

system · December 4, 2012, 4:03pm

Hi all,

We’re moving from Oracle to DB2.
In the oracle environment, I had a mapping, where I had to output “[ICP]” if it is a nullvalue.
This is the Oracle code(Mapping):
nvl(Validation_Movetype_Pass.HYP_ICP , ‘[ICP None]’)

So, I expect, when there’s a nullvalue, that the outupt is the following text:
[ICP None]

But I can’t make it run properly in DB2 environment. When I leave it unchanged, I get an empty Line.
No matter howe I change the code, DS either does not accept it - ging an error, or I just get an empty field/line.

How do I tell my DB2 that I need those square brackets?

Bajiman (BOB member since 2011-05-19)

system · December 4, 2012, 5:05pm

What is the error you get? Is that a NULL or a space is the new system?

ganeshxp (BOB member since 2008-07-17)

system · December 4, 2012, 6:25pm

You have an escape character there so that DS doesn’t try to evaluate the square bracket. I think you need an additional escape character so that DB2 also doesn’t try to evaluate the square bracket.

Try this:

nvl(Validation_Movetype_Pass.HYP_ICP , '\\\[ICP None\\\]')

eganjp (BOB member since 2007-09-12)

system · December 4, 2012, 10:17pm

try assigning this value to a global variable in a script and use the global variable in mapping

do the following in a script in the begining of the job
$G_ICP_VAL = ‘[ICP None]’;

use the following in mapping
nvl(Validation_Movetype_Pass.HYP_ICP , $G_ICP_VAL)

manoj_d (BOB member since 2009-01-02)

system · December 5, 2012, 9:14am

eganjp:

You have an escape character there so that DS doesn’t try to evaluate the square bracket. I think you need an additional escape character so that DB2 also doesn’t try to evaluate the square bracket.

Try this:
nvl(Validation_Movetype_Pass.HYP_ICP , '\\\[ICP None\\\]') 

Thanks for your input.
DS does accept this code, but nonetheless, I still get an in that field.

Bajiman (BOB member since 2011-05-19)

system · December 5, 2012, 9:35am

Hi Ganesh,

I do get an
…

Bajiman (BOB member since 2011-05-19)

system · December 5, 2012, 9:53am

Thanks for your idea!

I gave it a try, but still…no success…I only get an

So I started combining these great ideas you all gave me here, unfortunately without success…
I’m trying more combinations…
:?

Bajiman (BOB member since 2011-05-19)

system · December 5, 2012, 4:03pm

Try this one for me

IFTHENELSE(LTRIM_BLANKS(RTRIM_BLANKS(NVL(Validation_Movetype_Pass.HYP_ICP,’’))) = ‘’, ‘[ICP None]’, Validation_Movetype_Pass.HYP_ICP)

ganeshxp (BOB member since 2008-07-17)

system · December 5, 2012, 4:46pm

I think the problem is that no matter what has been tried we haven’t worked around DB2 doing something with the square brackets.

This works fine in a DS script:

print('This is \[nothing\]');

It prints:

So if DS doesn’t have a problem with it then the problem must be on the DB2 side. Focus your efforts there.

Moral to the story: Don’t use DB2. [/quote]

eganjp (BOB member since 2007-09-12)

system · December 6, 2012, 7:17am

Ganesh…guess what…it…works!
WONDERFUL!

Thank you very much

eganjp:

I think the problem is that no matter what has been tried we haven’t worked around DB2 doing something with the square brackets.

This works fine in a DS script:
print('This is \[nothing\]');
It prints:

This is [nothing]

So if DS doesn’t have a problem with it then the problem must be on the DB2 side. Focus your efforts there.

Moral to the story: Don’t use DB2.

[/quote]

About the morals…I do absolutely agree with you…I always tell my boss that I want to return to Oracle…

Bajiman (BOB member since 2011-05-19)

system · December 6, 2012, 2:32pm

Hah I am very glad it worked.

Well we do have the way to treat SAP NULL’s as NULL however, it looks like we really don’t have a way to treat DB2 NULL’s as NULL maybe?

ganeshxp (BOB member since 2008-07-17)