Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Usage of Casting on DB2 column

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

New User


Joined: 07 Dec 2006
Posts: 51
Location: Bloomington

PostPosted: Wed Sep 21, 2011 11:19 am    Post subject: Usage of Casting on DB2 column
Reply with quote

Friends,

I would like to know the usage of 'Casting' on a DB2 column.

I know we can use the 'Casting' for getting the column value up to the required length.

Ex: If the DB2 column is defined as PIC X(9) and if I want to retrieve just PIC X(5) length value from table I can use it like the following

SELECT CAST (column name AS CHAR(5)) from 'table name'

My question is " Can we use this 'Casting' in the following cases
1. Table column defined as COMP3 and if I want to get the value in COMP
2. Table column defined as CHAR and if I want to get the value in COMP-3
3. Table column defined as PIC S9(4) COMP and if I want to get the value
in PIC 9(1).

Please let me know if I can use the 'CASTING' for the above 3 cases.

Thanks and Regards,
Rajkumar Nerella
Back to top
View user's profile Send private message

Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 788
Location: Chennai, India

PostPosted: Wed Sep 21, 2011 11:38 am    Post subject:
Reply with quote

Those 3 cases are not valid.

COMP-3, COMP, PIC... are for COBOL data types, NOT in DB2.

DB2 has DECIMAL, INT, CHAR, etc as data types.

From the manual,
Quote:
Assume that an application needs only the integer portion of the SALARY column, which is defined as DECIMAL(9,2) from the EMPLOYEE table. The following query for the employee number and the integer value of SALARY could be prepared.

SELECT EMPNO, CAST(SALARY AS INTEGER) FROM EMPLOYEE;


Please refer this.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Sep 21, 2011 2:09 pm    Post subject:
Reply with quote

as Gnana Sekaran Nallathambi indicated,
db2 datatypes are not those that you stated.

what you stated (comp, comp3, PIc 9) are cobol USAGE types.

from the manuals links above this table indicates Supported casts between built-in data types

PIC 9 would be a CHAR(1).

Though DB2 can perform all this stuff,
if your results set is many rows,
it could be faster to do the datatype conversion in you program code
if this is imbedded sql.

if this is for an unload type operation, you will have to construct the sql to perform the datatype conversion.

a couple of notes:
going from decimal to integer (comp3 with DP to binary) you will loose anything to the right of the decimal
going from CHAR to DECIMAL (char to comp3 with db) you need to insure the CHAR string it appropriate and define the precision
going from Binary to CHAR (comp to pic 9) you will need to SUBSTR the appropriate character.
Back to top
View user's profile Send private message
Rajkumar_n

New User


Joined: 07 Dec 2006
Posts: 51
Location: Bloomington

PostPosted: Wed Sep 28, 2011 2:18 pm    Post subject: Usage of Casting on DB2 column
Reply with quote

I really appreciate every one for helping me.

Thanks,
Rajkumar
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 how can i move s9(9) to s9(9) usage comp HARENDRA CHOUDHARY COBOL Programming 3 Mon Nov 06, 2017 12:10 am
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us