Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2009
    Posts
    26

    Unanswered: Need Help to create a query to eliminate data types

    Hello EveryOne,
    I am struggling a lot for a simple query which i am unable to get it..
    Currently i am using the following query to get the primary keys of a table..

    "SELECT NAME FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = ? AND TBCREATOR=? AND KEYSEQ > 0 ORDER BY KEYSEQ ASC";

    But in the primary key i may have like many columns But i am trying to eliminate the "BLOB" data type....
    I wanted to get the result with out any blob data types..

    I am using other query which is simple ..
    Select * from tablename;

    In this query also i wanted to eliminate the blob data type..



    how to eliminate the column which has a data type of "BLOB"...
    I would really appreciate if some one can give me the query..

    Thanks,
    swati

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not understand your question. Try rephrasing it. Also supply your DB2 version and OS.

    Andy

  3. #3
    Join Date
    Oct 2004
    Posts
    268
    Some one else may have a better one but try this.........Replace YourschemaName with the actual schema name unless you want them all.

    db2 "SELECT substr(name, 1,30)as NAME, COLTYPE FROM SYSIBM.SYSCOLUMNS where TYPESCHEMA = 'YourschemaName' and coltype <> 'BLOB'order by name"

  4. #4
    Join Date
    Jul 2009
    Posts
    26
    Thanks for your quick response.
    Sorry for the in convince...
    My main goal of my task is ...I have to get all the rows of the table first .And then after some time again i have to get all the rows of the same table..and compare the resluts if any changes happened i have to report it.That is the mail task..

    I have written my code everything was wokring fine i tested with only varchar data type of B2 was happy...

    But when i started testing with a table where i have blob data type..There i got exceptions and have done some research ..I figured out some thing is we can not compare the blob data types..

    "selct * from table name"
    this is the query i am using to retrieve all the rows and columns of the table..
    Which includes blob data type..

    I wanted to get the result set which excludes the blob data type columns...

    So it can be achived my a sql query only where we should be able to specify we dont need the blob data type..

    To write a query in DB2 which will exclude all the blob data type columns and should return me the result with out blob data types..

    Thats the question ...

    Let me know if you still dont undestand the question..

  5. #5
    Join Date
    Feb 2009
    Posts
    114
    WOW!! How about excluding those columns whose data type you do not like from your select list

  6. #6
    Join Date
    Jul 2009
    Posts
    26
    Thanks a lot for your quick response...For excluding the columns whose data type i dont like it..What could be the query for that...

    I would really appreciate if some one can tell the query .......

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by swati malla
    Thanks a lot for your quick response...For excluding the columns whose data type i dont like it..What could be the query for that...

    I would really appreciate if some one can tell the query .......
    Simple, instead of: select * from table
    try: select col1,col2,col3,col4... from table

    Andy

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ARWinner
    Simple, instead of: select * from table
    try: select col1,col2,col3,col4... from table

    Andy
    In Texas, DBA's are allowed to shoot programmers who use "select *".
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DBA's are allowed to shoot programmers who use "select *".
    Except "EXISTS(SELECT * ...)".
    Last edited by tonkuma; 09-16-09 at 10:31.

  10. #10
    Join Date
    Jul 2009
    Posts
    26
    Thanks a lot guys for posting answers...

Posting Permissions

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