how to convert clob to other data type ?

[t2581]

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_LOCATIO N INTEGER NOT NULL ,
CUSTOMER_NUMBER CHAR(16) NOT NULL ,
LAST_UP_DATE_TI ME 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.NOTEPA D" cannot be CAST to type
"SYSIBM.VARCHAR ". SQLSTATE=42846
Result of select has to be imported in to sql server

thanks

Alex

[Tonkuma]

t2***@hotmail.c om 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_LOCATIO N INTEGER NOT NULL ,
CUSTOMER_NUMBER CHAR(16) NOT NULL ,
LAST_UP_DATE_TI ME 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.NOTEPA D" 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

[Serge Rielau]

Tonkuma wrote:

t2***@hotmail.c om 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_LOCATIO N INTEGER NOT NULL ,
CUSTOMER_NUMBER CHAR(16) NOT NULL ,
LAST_UP_DATE_TI ME 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.NOTEPA D" cannot be CAST to type
"SYSIBM.VARCHA R". 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

[t2581]

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.c om 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_LOCATIO N INTEGER NOT NULL ,
CUSTOMER_NUMBER CHAR(16) NOT NULL ,
LAST_UP_DATE_TI ME 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.NOTEPA D" 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