473,842 Members | 1,887 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_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

Nov 16 '06 #1
3 57564

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

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
48711
by: Karthik | last post by:
Hello, How can I convert a BSTR data type to std::string??? Thanks a much! Karthik
0
1011
by: Steve Barnett | last post by:
Hi, I'm using GetSchemaTable() to retrieve the schema information for a series of tables in a database. One of the columns returned to me is the "DataType" column, which gives me the .NET data type of the column. What I want is the equivalent data type that would be used in the SQL definition of the column, were you to define it in a create table command. Is there any way to get .NET to convert it's data type in to an SQL data type? For...
2
1261
by: authorking | last post by:
How can I convert a string data type to a int data type.
2
1504
by: Matias | last post by:
Hi: I need some help, about How can I define a GLOBAL Data type in ASP.net proyect. thanks
4
5760
by: Hon Yuen, Ng | last post by:
Hi This is a newbie question. I've a datatable that contains null value. Sometime i need to store this data into different variables. However, error will be thrown if the data i tried to stored in the variable is a null value. E.g. Dim int As Int64 Dim null As Object
0
1370
by: Jimm | last post by:
Can anyone advise me as to the best method for updating an Oracle CLOB data type. I have to update a single column of CLOB data type via a web service. Thank you in advance. Jimm.
2
6276
by: Sisnaz | last post by:
I'm reading byte information from a C++ program via TCP sockets. At one time I thought I came across away to convert C Float data type into a VB single data type. I'm reading the C data type in bytes from the socket stream but can't seem to convert it. Any help would be greatly appreciated. Here is my data: The actual values in the C++ application: Node 1 LL40 - Position
5
3411
by: rAinDeEr | last post by:
Hi, I have a web application with a table to store terms and conditions of a Company. This may some times run into many pages and some times it may be just a few sentences. It is a character text field. I want to know which Data type I need to use so that it doesnt waste memory. thanks in advance, rAinDeEr
7
6386
by: eneyardi | last post by:
Is there anyway to sort fields which data type is memo, because unlike other data type can sort. Any suggestions are highly appreciated.
0
9870
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10610
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
7854
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7030
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5695
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5882
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4499
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4087
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3141
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.