Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB22 casting to CHAR

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Nirav721

New User


Joined: 11 Feb 2008
Posts: 53
Location: NJ

PostPosted: Wed Mar 19, 2008 8:16 pm    Post subject: DB22 casting to CHAR
Reply with quote

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
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Mar 19, 2008 8:56 pm    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
Nirav721

New User


Joined: 11 Feb 2008
Posts: 53
Location: NJ

PostPosted: Wed Mar 19, 2008 9:10 pm    Post subject: Reply to: db2 casting
Reply with quote

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...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Wed Mar 19, 2008 10:19 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Mar 19, 2008 10:26 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Nirav721

New User


Joined: 11 Feb 2008
Posts: 53
Location: NJ

PostPosted: Wed Mar 19, 2008 11:03 pm    Post subject: Reply to: DB22 casting to CHAR
Reply with quote

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?
Back to top
View user's profile Send private message
Nirav721

New User


Joined: 11 Feb 2008
Posts: 53
Location: NJ

PostPosted: Thu Mar 20, 2008 12:46 am    Post subject: Reply to: DB22 casting to CHAR
Reply with quote

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
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Mar 20, 2008 1:05 am    Post subject: Reply to: DB22 casting to CHAR
Reply with quote

You're welcome - good luck icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Casting a Binary Number to Numeric in... Bob Steinkraus Java & MQSeries 6 Tue Aug 09, 2016 5:58 pm
No new posts conversion from char to packed decimal Musab Ahmed DFSORT/ICETOOL 7 Fri Apr 08, 2016 1:12 pm
No new posts Converting field from char to number danylele74 DB2 4 Tue Mar 29, 2016 4:24 pm
No new posts Converting s9(9) comp field to char l.nethaji DFSORT/ICETOOL 3 Fri Jan 22, 2016 12:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us