Thanks a lot
both suggestions works.
In my case I had to use function
CREATE FUNCTION APP.ALEX01(str APP.NOTEPAD) RETURNS varchar(8000)
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN CAST(CAST(str AS CLOB(4096)) as varchar(8000)) ;
I am transfering data using Microsoft SSIS with Microsoft ole db
provider for DB2
when column simply casted in select statement as
select CAST(CAST(note AS CLOB(4096)) as varchar(6000)) from
APP.AP_NOTE_PAD
values in column converted by SSIS to binary data!!!
when statement use function as
select APP.ALEX01(note) from APP.AP_NOTE_PAD
values in column converted by SSIS to characters
Serge Rielau wrote:
Tonkuma wrote:
t2***@hotmail.com wrote:
Hi ,
I have database db2 udb 7.2
with following table and datatype
CREATE DISTINCT TYPE APP.NOTEPAD AS SYSIBM .CLOB(4096);
CREATE TABLE APP.AP_NOTE_PAD (
CONTROL_LOCATION INTEGER NOT NULL ,
CUSTOMER_NUMBER CHAR(16) NOT NULL ,
LAST_UP_DATE_TIME TIMESTAMP NOT NULL ,
NOTE_TITLE CHAR(60) NOT NULL ,
LAST_UP_OPID CHAR(8) NOT NULL ,
NOTE_TYPE SMALLINT NOT NULL ,
WORKLIST_IND CHAR(1) NOT NULL ,
NOTE APP.NOTEPAD LOGGED NOT COMPACT NOT NULL )
IN TSMI011 ;
I need to convert column Note into varchar or any other string
select CAST(note as varchar(6000)) from APP.AP_NOTE_PAD
SQL0461N A value with data type "APP.NOTEPAD" cannot be CAST to type
"SYSIBM.VARCHAR". SQLSTATE=42846
Result of select has to be imported in to sql server
thanks
Alex
select CAST(CAST(note AS CLOB(4096)) as varchar(6000)) from
APP.AP_NOTE_PAD
Alex,
too shorten this take a look at CREATE FUNCTION (sourced).
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
