| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
09-17-10, 01:36
|
|
Registered User
|
|
Join Date: Sep 2010
Posts: 6
|
|
|
Reading raw datatype in native sql
|
|
I am trying to read data by joining tables from based on raw datatype column in sql query.
For eg: Select Col1, Col2 from table1 a, table2 b where a.col3 = b.col4
output of query = 0 Rows
Col3 and col4 defined as raw datatype in database. Please let me know how to resolve this issue. Any help is appreciated
|
|
09-17-10, 08:04
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
What do you mean by "raw datatype"? What DB2 version and OS are you using?
Andy
|
|
09-20-10, 02:45
|
|
Registered User
|
|
Join Date: Sep 2010
Posts: 6
|
|
|
|
I am not very sure but it is SAP backend. we are trying to download some data from backend based on query. OS is UNIX.
|
|
09-20-10, 08:07
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
What do you mean by "raw datatype"?
Andy
|
|
09-21-10, 02:34
|
|
Registered User
|
|
Join Date: Sep 2010
Posts: 6
|
|
I checked form SAP front end the datatype of the column. It says raw as a datatype of the physical field.
|
|
09-21-10, 08:31
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
Can you look in the database for the data type? DB2 has no data type that is "raw".
Andy
|
|
09-24-10, 03:15
|
|
Registered User
|
|
Join Date: Sep 2010
Posts: 6
|
|
I check at the backend table and found datatype mentioned for the column is CHARACTER with length of 16. But Actualy it stroes values of 32 bytes.
|
|
09-27-10, 08:23
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
Obviously you are not looking in the right place. You cannot store 32 bytes is a CHAR(16) field.
Andy
|
|
09-27-10, 14:41
|
|
Registered User
|
|
Join Date: Sep 2010
Posts: 6
|
|
I check in backend and it shows datatype as CHARACTER with length16. I am not able to see the data on the backend, but when I see data in SAP front end, I could see 32 some values like below
02018B4C780A9B18E1000000A3EC0393
02188B4C62159B18E1000000A3EC0393
023B8B4C8C109B18E1000000A3EC0393
|
|
09-27-10, 14:47
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
Either you are looking at the wrong thing, or SAP is adding something to the column when you get to see it.
Andy
|
|
09-27-10, 15:42
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
Run the following SQL, substituting the actual table and column names for both columns in question:
Code:
select tabschema, tabname, colname, typename, length, scale, codepage
from syscat.columns
where tabschema='YOURSCHEMA' and tabname='YOURTABLE' and colname='YOURCOLUMN'
|
|
09-27-10, 20:44
|
|
Registered User
|
|
Join Date: May 2003
Location: USA
Posts: 5,196
|
|
You might also be able to run the following:
describe table schema.table-name show detail
"show detail" is optional
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
|
|
09-28-10, 01:46
|
|
Registered User
|
|
Join Date: Sep 2010
Posts: 6
|
|
Thanks for all the replies, but I got the solution as below
SELECT COL1, VARCHAR_FORMAT_BIT(CAST(Col2 as VARCHAR2(16) as bit format), 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX') FROM tabl1
|
|
09-28-10, 08:09
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
Quote:
Originally Posted by xs2shishir
I got the solution as below
SELECT COL1, VARCHAR_FORMAT_BIT(CAST(Col2 as VARCHAR2(16) as bit format), 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX') FROM tabl1
|
Doesn't seem to be related to your original question, but you're the boss...
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Reading raw datatype in native sql
