Joined: 07 Dec 2006 Posts: 51 Location: Bloomington
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.
Joined: 06 Sep 2007 Posts: 788 Location: Chennai, India
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,
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;
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.