Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2007
    Posts
    2

    Unanswered: Summarizing data into another table & integer comparisons question

    Hi everyone! It's my first time on this forum, so I hope I'm posting in the correct place for my question (it seems to be since I'm using DB2). My question pertains to SQL and has been perplexing me as I am relatively new to non-trivial (at least to me) queries.

    The problem: I have the following table
    Code:
    stocks          {
                 symbol      string 
                 shares       integer
                 price         float
                 time          integer          (this is in hhmmss format..1:59:43 AM is written 005943)
                                }
    And I want to use this data to create this table:
    Code:
    avgprice        {
                 symbol       string
                 interval      integer     (this is summarized to 0= 12 AM to 12:59:59 and so on...)
                 avgprice    float
                                }
    My two questions are: Can I use the timestamp feature of DB2 to work with the integer in the format of hhmmss (I don't believe I can, but I was just wondering). If not, what is an easy way for me to correlate 000000-005959 to 0 in the avgprice table?(I want to pull out the leftmost two digits of the integer in the stocks table to work with them.)

    Also, since there are 24 hours in the day, will I need 24 seperate queries for each interval or is there an easier way to iterate through. I don't believe I can use wildcards with integer types so I'm lost on this.

    Thanks for all your help and let me know if I have to clear anything up. I'm writing this in kind of a rush.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 does not have a string data type. Are you sure you are using IBM's DB2 database.

    Maybe this is a homework assignment.

    One hint: look at the CASE statement.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2007
    Posts
    2
    Hi Marcus_A,

    According the O'Rielly SQL Cookbook DB2 does indeed support a string data type. Though it is not nearly as robust as a true programming language, it does have it. Take for example the REPLACE and TRANSLATE functions that remove unwanted characters from strings. I mean unless you like to call them seperate characters. Anyway, I looked in this book as well and didn't see anything pertaining to a CASE statement.

    Is this board for IBM onlY?! I thought it was for all DB2, since that is not specified in the forum header dBforums > Database Server Software > DB2 says nothing about IBM specific.

    So, I'm still wondering what is going on here and waiting for a useful response. Thanks.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 is a product of the IBM company. I thought that maybe you confused DB2 with some other product, such as dBase or something else.

    If you look at the DB2 SQL Reference, Vol 2 for CREATE TABLE, you will see that you cannot create a table with a string data type. Even if you could, you would have to specify the length.

    Since you have apparently not ever consulted the DB2 SQL Reference manual (Vol 1 or Vol 2), then it just seems somewhat doubtful that you are working with IBM's DB2.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    May 2006
    Posts
    82
    I agree with Marcus. Up to my knowledge in DB2 there is no datatype by name STRING in DB2. DB2 is a registered trademark of International Business Machines Corporation. Where ever you refer to DB2 UDB its IBM. I dont think you are using DB2 RDMS. If yes, please give the details. On which O.S it is running..? Which version and fixpack and more details like product no etc..if possible.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by vini_srcna
    I agree with Marcus. Up to my knowledge in DB2 there is no datatype by name STRING in DB2. DB2 is a registered trademark of International Business Machines Corporation. Where ever you refer to DB2 UDB its IBM. I dont think you are using DB2 RDMS. If yes, please give the details. On which O.S it is running..? Which version and fixpack and more details like product no etc..if possible.
    He must be doing a homework assignment from school.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Iasthaai
    Hi Marcus_A,

    According the O'Rielly SQL Cookbook DB2 does indeed support a string data type. Though it is not nearly as robust as a true programming language, it does have it. Take for example the REPLACE and TRANSLATE functions that remove unwanted characters from strings. I mean unless you like to call them seperate characters. Anyway, I looked in this book as well and didn't see anything pertaining to a CASE statement.

    Is this board for IBM onlY?! I thought it was for all DB2, since that is not specified in the forum header dBforums > Database Server Software > DB2 says nothing about IBM specific.

    So, I'm still wondering what is going on here and waiting for a useful response. Thanks.
    If you want to have a good and useful reference, I suggest that you refer to DB2's online documentation: http://publib.boulder.ibm.com/infoce...w/v8/index.jsp

    There you will finde that the data types CHAR (short for CHARACTER) and VARCHAR (short for CHARACTER VARYING) are available to store character data (commonly known as strings). There is no such data type as STRING in DB2 - and neither in many other database management systems.

    A simple query against SYSCAT.DATATYPES can be used to confirm that.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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