Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2010
    Posts
    6

    Unanswered: 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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What do you mean by "raw datatype"? What DB2 version and OS are you using?

    Andy

  3. #3
    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.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What do you mean by "raw datatype"?

    Andy

  5. #5
    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.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Can you look in the database for the data type? DB2 has no data type that is "raw".

    Andy

  7. #7
    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.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Obviously you are not looking in the right place. You cannot store 32 bytes is a CHAR(16) field.

    Andy

  9. #9
    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

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Either you are looking at the wrong thing, or SAP is adding something to the column when you get to see it.

    Andy

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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'

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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

  13. #13
    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

  14. #14
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by xs2shishir View Post
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •