By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,537 Members | 1,750 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,537 IT Pros & Developers. It's quick & easy.

how to convert clob to other data type ?

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a

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

P: n/a
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

P: n/a
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.