|
View previous topic :: View next topic
|
| Author |
Message |
mighty
New User
Joined: 21 May 2008
Posts: 26
Location: chennai
|
|
|
|
| In DB2 how to divide a table in to exactly half. |
|
| Back to top |
|
 |
mighty
New User
Joined: 21 May 2008
Posts: 26
Location: chennai
|
|
|
|
| Sorry,the exact question is In DB2 how can u divide a table in to exactly half? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| what do you mean 'in half?'. |
|
| Back to top |
|
|
mighty
New User
Joined: 21 May 2008
Posts: 26
Location: chennai
|
|
|
|
| A table is having some n number of records,so i need exactly n/2 records in my output file.How can we achieve this. |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
Hi,
| Code: |
SELECT COUNT *
FROM TABLE
INTO VAR1
SELECT *
FROM TABLE
FETCH FIRST VAR1 ROWS ONLY
|
Hope this would help you. |
|
| Back to top |
|
|
Aaru
Senior Member
Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India
|
|
|
|
bharath,
| Quote: |
SELECT COUNT *
FROM TABLE
INTO VAR1
SELECT *
FROM TABLE
FETCH FIRST VAR1 ROWS ONLY |
Did you test this SQL? |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
Sorry Aaru, I became a bit complacent.
| Code: |
SELECT COUNT *
FROM TABLE
INTO VAR1
COMPUTE VAR2 = VAR1 / 2.
SELECT COUNT *
FROM TABLE
FETCH FIRST VAR2 ROWS ONLY
|
Would this solve mighty's problem? |
|
| Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
|
|
So much for a solution, but why would you need to "divide a table in to exactly half"?
What if there is an odd number of rows? |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Quote: |
| .... but why would you need to "divide a table in to exactly half"? |
/standard answer on
that' s the requirement
/standard answer off 
| Quote: |
| What if there is an odd number of rows? |
for that we appointed a committee to build a business case to implement it in the next release |
|
| Back to top |
|
|
Aaru
Senior Member
Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India
|
|
|
|
bharath,
| Quote: |
| Sorry Aaru, I became a bit complacent. |
Not a Problem. But did u test this before posting? were you able to divide the table?
As far as i know COUNT should be coded as COUNT(*) and not as shown in your SQL.
I also doubt whether the INTO keyword is acceptable.
Cics Guy,
Good Question. |
|
| Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
|
|
| enrico-sorichetti wrote: |
| Quote: |
| .... but why would you need to "divide a table in to exactly half"? |
/standard answer on
that' s the requirement
/standard answer off
| Quote: |
| What if there is an odd number of rows? |
for that we appointed a committee to build a business case to implement it in the next release |
ROTFL....  |
|
| Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia
|
|
|
|
| The commitee must await the next budget cycle. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
All of this and not a table divide in sight. . .
| Quote: |
| so i need exactly n/2 records in my output file. |
Please show an example of some table with 10 rows in it and how you want 1/2 of them in an output file.
If you were to run this on 5 consecutive days should the same 1/2 always be selected? Said differently, do inserts & deletes matter?
What are you trying to accomplish? |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
Unload
use sort to create two new files.
load (twice) |
|
| Back to top |
|
|
mighty
New User
Joined: 21 May 2008
Posts: 26
Location: chennai
|
|
|
|
We will be doing inserts and updates after that and we have so many criteria after that like in one half we need to sort and find out some data and so on.......
So i just wanted a query to find out exactly 1/2 records of the table.
Thanks for all ur updates...... |
|
| Back to top |
|
|
the_gautam
Active User
Joined: 05 Jun 2005
Posts: 165
Location: Bangalore
|
|
|
|
I ve not checked it on mainframes. However, it is working for me on plain SQL. My table was having an ascending TIMESTAMP column.
| Code: |
SELECT * FROM MYTABLE A WHERE (SELECT COUNT(*) / 2 FROM MYTABLE B) > (SELECT COUNT(*) FROM MYTABLE B WHERE A.MYTIMESTAMP>B.MYTIMESTAMP)
|
|
|
| Back to top |
|
|
mighty
New User
Joined: 21 May 2008
Posts: 26
Location: chennai
|
|
|
|
Gautam i dont think it will work for me.Beacause none of my coloumns is sorted in any order.
_____________
Thanks,
Mighty |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
We will be doing inserts and updates after that and we have so many criteria after that like in one half we need to sort and find out some data and so on.......
So i just wanted a query to find out exactly 1/2 records of the table. |
If you continue to refuse to provide your actual requirement, we can't help you. You may believe you have provided proper info for us to make suggestions - you have not done so.
You have not defined what 1/2 means and you have not defined what you want to do with the 1/2.
If you can't or won't post some example data and how you want the process to work we will need to close this topic. |
|
| Back to top |
|
|
mighty
New User
Joined: 21 May 2008
Posts: 26
Location: chennai
|
|
|
|
Thanks all for your help.
Thank you Goutam, its working for me with the query which you have provided... |
|
| Back to top |
|
|
|
|
