469,945 Members | 1,660 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,945 developers. It's quick & easy.

how to convert clob to other data type ?

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

Nov 16 '06 #1
3 53417

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

Nov 17 '06 #2
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
Nov 17 '06 #3
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
Nov 17 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Karthik | last post: by
reply views Thread by Steve Barnett | last post: by
2 posts views Thread by authorking | last post: by
2 posts views Thread by Matias | last post: by
reply views Thread by Jimm | last post: by
5 posts views Thread by rAinDeEr | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.