Summarizing data into another table & integer comparisons question

Iasthaai · #1 (**permalink**) 02-04-07, 15:28

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.

Marcus_A · #2 (**permalink**) 02-04-07, 18:09

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.

Iasthaai · #3 (**permalink**) 02-04-07, 23:48

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.

Marcus_A · #4 (**permalink**) 02-05-07, 01:22

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.

vini_srcna · #5 (**permalink**) 02-05-07, 01:40

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.

Marcus_A · #6 (**permalink**) 02-05-07, 01:56

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.

stolze · #7 (**permalink**) 02-06-07, 04:58

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech