View previous topic :: View next topic
|
Author |
Message |
Rajkumar_n
New User
Joined: 07 Dec 2006 Posts: 51 Location: Bloomington
|
|
|
|
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 |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007 Posts: 792 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,
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
Rajkumar_n
New User
Joined: 07 Dec 2006 Posts: 51 Location: Bloomington
|
|
|
|
I really appreciate every one for helping me.
Thanks,
Rajkumar |
|
Back to top |
|
|
|