DB22 casting to CHAR

Nirav721 · New User Joined: 11 Feb 2008 Posts: 53 Location: NJ

Hi,

I have a dilemma...

I have a decimal(9,2) field and I am using a IFNULL function inside my select to replace null fields with X'FF'. And to do this, I am using casting to CHAR. Now, I am doing cast to CHAR(11) to make sure nothing gets truncated, but my host variable is defined as only PIC X(5)... Is there a way to get around this?

I want to be able to move X'FF' inside the host variable which is PIC X(5) for a DECIMAL(9,2) column when it is NULL?

Any help is appreciated

Thanks

Craq Giegerich · Posted: Wed Mar 19, 2008 8:56 pm

Why are you trying to sustitute high values for a numeric field even if it is blank? That is what null indicator fields are for. How could you expect to fit a char(11) field into a pic x(5) field?

Nirav721 · New User Joined: 11 Feb 2008 Posts: 53 Location: NJ

I need to move high-values in so that the modules that have already been coded need not be changed... PIC X(5) would be the packed decimal value...?

Hope this clears it a bit..

Also, another question

I know I can do

SELECT CASE <COLUMN>
WHEN <COLUMN> THEN
....
ELSE
....
END

But, what can I put in to do

SELECT CASE <COLUMN>
WHEN not <COLUMN> THEN
....
ELSE
....
END

It is flagging me on the NOT part....

Thanks for the help...

dbzTHEdinosauer · Posted: Wed Mar 19, 2008 10:19 pm

As Craig said, you can't load non numerics to a numeric DB2 column.

even if you could, you would have to turn off the null ind for the column.

please get back if you can accomplish this.

I don't think that you can.

besides X'FF' is not a valid packed-decimal field.

dick scherrer · Posted: Wed Mar 19, 2008 10:26 pm

Hello,

You need to rethink the implementation.

Keep in mind that even if you do find a way to force the invalid value into the column, you will be forever trying to get around the problems it will cause when something tries to use it.

Nirav721 · New User Joined: 11 Feb 2008 Posts: 53 Location: NJ

Hi Thanks, yep, I realize it.

So, what I have done now is, I initialize the whole host variable area to high-values, and then do a check such as

CASE WHEN <COLUMN1> IS NOT NULL
IFNULL(<COLUMN1>,0) <-- this does absolutely nothing.
END AS <COLUMN1>

This way, I only move the field in if there is actually value in it, if not, it does not move it inside my host-variable area, leaving X'FF's intact...

What do you guys think?

Nirav721 · New User Joined: 11 Feb 2008 Posts: 53 Location: NJ

Actually, I need not even do the last part because DB2 takes care of moving part...All this time I was under the impression that DB2 does move some sort of data when a column is null...but it doesnt..so I need not do anything at all....

Thanks

dick scherrer · Posted: Thu Mar 20, 2008 1:05 am

You're welcome - good luck