Create view with cast problem

Carol A. DOC Hynes

Create view with cast problem
I am using DB2 Connect EE 7.2 with a federated database to connect to an
Oracle 7.3 database. I'm creating a view using the following:

CREATE view DB2ADMIN.WITS_INMATE (IO_TYPE, IO_ID,.....) AS SELECT 'I',
cast(Offenders.ID as Char(8)), ...........

Offenders.ID is defined in ORACLE as NUMBER(8). I must have it in character
format for processing a vendor application.

My problem is that the cast function appears to truncate the leading zeros
before converting the data to character. I need to have the leading zeros
included in the value. Can anyone suggest how I might keep the leading
zeros and still convert the data to character?



Terry Purcell

Re: Create view with cast problem
(in response to Carol A. DOC Hynes)
Carol,

I have no experience with accessing Oracle via a federated database,
although if I was to convert a numeric to a character, and maintain the
leading zeros, I would use the DIGITS function.

Such as DIGITS(Offenders.ID). This can then be wrapped in the CHAR function
if the result is not CHAR(8).

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Hynes, Carol A. DOC
Sent: Friday, December 28, 2001 10:01 AM
To: [login to unmask email]
Subject: Create view with cast problem


I am using DB2 Connect EE 7.2 with a federated database to connect to an
Oracle 7.3 database. I'm creating a view using the following:

CREATE view DB2ADMIN.WITS_INMATE (IO_TYPE, IO_ID,.....) AS SELECT 'I',
cast(Offenders.ID as Char(8)), ...........

Offenders.ID is defined in ORACLE as NUMBER(8). I must have it in character
format for processing a vendor application.

My problem is that the cast function appears to truncate the leading zeros
before converting the data to character. I need to have the leading zeros
included in the value. Can anyone suggest how I might keep the leading
zeros and still convert the data to character?