How can you split a table in to exactly half?
Answers were Sorted based on User's Feedback
Answer / nirmal khatri
Hi
We can do it by SQL also, below is SQL which is working on
iSeries(AS/400)
insert into nk11581/temp1 (select * from nk11581/temp1
where rrn(temp1) in(
SELECT distinct dec((rrn(temp1)/2)) FROM nk11581/temp1
))
Is This Answer Correct ? | 3 Yes | 1 No |
Answer / reddy kanupuru
Hi,
Here i am giving the procedure , how to make the table into
half..... using program... I dont know whether any direct
SQL command is there or not. This is just for ur idea what
i know.
1) Find the total number of rows in a table using count(*)
2) Declare two variable in working storage section.
eg: 02 A pic 9(4) .
02 B pic 9(2).
store the count no into one variable .assume u stored that
in A.
then divide that by 2. so u will get half of the rows in a
table. strore that value in B.
3) Take another variable name C .
eg: 02 c pic 9(4) value 0.
perform para1 until B =C
para1:
Retrive the row from the table.
move to corresponding host variables ( use table to store
muliple rows ).
insert into table(u can insert multiple rows at a time ...)
do the same for storing the other half into the another
table.
means here 1 st half rows were stored in one temp
table .... the other in the one table.
Is This Answer Correct ? | 4 Yes | 3 No |
Answer / pavani
i forgot to mention the c value increment in the above
mail.increment the c value after each row retrieved.
Is This Answer Correct ? | 2 Yes | 1 No |
Answer / sachin
ADDING TO ans1
no need to write perform
direct use below query
select * from table fech first :b rows only.
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / arpita
We can achieve this by using STRSQL and CPYF commands as
follows -
Type STRSQL, Press Shift + F1, option1 Change session
attributes.Type 3 for SELECT option, specify the output
file name and library (must be created in QTEMP, for a
temporary file). Press ENTER twice.
Then, type the following query -
select * from SAHAA/EMPDATA1
where rrn(EMPDATA1) in(
SELECT distinct dec((rrn(EMPDATA1)/2)) FROM SAHAA/EMPDATA1
) --> use your file and library name
Press Enter, the message File TEMP in QTEMP was created.
Restore the previous session attributes as follows -
Press Shift + F1, option1 Change session attributes.Type 1
for SELECT option. Press ENTER thrice.
Now, we would have half no. of records of EMPDATA1 in file
TEMP (say)
Now, simply use the CPYF command to copy TEMP file data
into EMPDATA1 as follows -
CPYF FROMFILE(QTEMP/TEMP) TOFILE(SAHAA/EMPDATA1) MBROPT
(*REPLACE)
Note:- TEMP is the file temporarily created in QTEMP, so
that it is no longer present in the system when the session
expires.
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / kumar
Hi ............
try this one ....might be a logical one ... but have to
give customers what they want.....
Select count(*) into :ws-count from emp with ur;
Declare CURSOR cursor1 for
Select *(if possible better code all fields)
from emp where count(*) < :ws-count;
Open cursor
Fetch cursor.
Declare CURSOR cursor2 for
Select *(if possible better code all fields)
from emp where
count(*) >= :ws-
count;
Open cursor
Fetch cursor.
Is This Answer Correct ? | 0 Yes | 4 No |
What is the difference between IN subselects and EXISTS subselect?
Hi Everyone... Under the Logical files, when am working on multiple rec format logical file, I have joined two pf's using the multiple record format logical file concept, but when i run queried the LF only the first mentioned pf records are reflecting and I am not able to see any of the field records specified under the second pf. Kindly let me know whats the reason behind this. Below is the str of LF., R rec PFILE(LOGICAA) CUSTNO CUSTNAME BILL K CUSTNO R rec1 PFILE(LOGICAA1) CUSTNO ADD K CUSTNO .....
What is the significance of the CURSOR WITH HOLD clause in a cursor declaration?
Explain various types of locks in db2?
List down the data types in the db2 database.
db2 maintains information about the data... a.in tables. b.in a set of tables known as db2 catalog. c.in db2 database. d.none of the above.
How do you retrieve the data from a nullable column?
What is role in db2?
What is db2 isolation?
What are union and union all?
Where is the output of EXPLAIN stored?
This was related to -811 sqlcode, In a COBOL DB2 program which accesses employee table and selects rows for employee 'A', it should perform a paragraph s001-x if employee 'A' is present. In this case it gets -811 sqlcode, but still it process the paragraph s001-x. What could be wrong in my code.