Do I need temporary table? [message #405181] |
Tue, 26 May 2009 14:51  |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
By calling a procedure with some input parameter it returns resulset.
say the resultset from procedure is
node branch leaf
111 1 7
111 2 7
111 3 7
now take the maximum branch i.e, 3
select that node at random from exiting table A that have 3 rows.say there are 3 rows and the node that have 3 rows are
1 and 2
pick any random rowset from table A
and replace node by 111 and the leaf value where fetch value of resultset = branch of table A...
When updating the values fetching values from resultset can go in only one node (1 or 2).so either rows of node 1 or 2 will be updated. 3 rows should be updated
Thanks
|
|
|
Re: Do I need temporary table? [message #405184 is a reply to message #405181] |
Tue, 26 May 2009 16:16   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
You have asked enough questions and been given enough responses to know what is expected of you now. Please follow the guidelines and what you have already been told.
|
|
|
Re: Do I need temporary table? [message #405374 is a reply to message #405184] |
Wed, 27 May 2009 08:13   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
when calling proc dummy Refcursor returns
Node Branch leaf v_col1 v_col2 v_col3
1 1 6 6789 3456 999
1 2 7 999 0 1
1 3 8 666 999 8
Which indicates the node 1 has maximum 3 branches
Now there is a table node_list where some combinations are being stored for node
NODE BRANCHES COL3 COL4 COL5 COL6
7 1 456 789 999 222
7 2 466 779 999 223
7 3 456 789 999 224
2 1 456 789 999 228
2 2 466 779 999 227
3 1 456 789 999 225
3 2 466 779 999 222
3 3 456 789 999 224
4 1 456 789 990 224
No In my main procedure I call the procedure which returns the above resultset
for some input parameters.
for some input values it may be more or less then 3 brances for node
Now get the node (in this example it will be 7 or 3) randomly from node_list which
have maximum brances equals to the max branch for the node returned by refcursor above.
replace the node for those 3 rows by node variable retunrned from fetch refcursor
and also col3 by v_col1, COL4 BY v_col2 COL5 BY V_COL3 ,COL6 (without any change)
into a new table.
But for each loop while inserting it should not pick entry from rows of node 3
if we picked node 7.
[ Please ignore this message till it gets formatted ]
[Updated on: Wed, 27 May 2009 08:37] Report message to a moderator
|
|
|
|
Re: Do I need temporary table? [message #405385 is a reply to message #405181] |
Wed, 27 May 2009 08:42   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
when calling proc dummy1 Refcursor returns
REFCURSOR RESULT:
NODE BRANCHES leaf v_col1 v_col2 v_col3
----- ---------- ---------- ---------- ---------- ----------
1 1 7 781 909 222
1 2 8 771 919 223
1 3 12 791 922 224
Which indicates the node 1 has maximum 3 branches
Now there is a table node_list where some combinations are being stored for node
NODE BRANCHES COL3 COL4 COL5 COL6
---------- ---------- ---------- ---------- ---------- ----------
7 1 456 789 999 222
7 2 466 779 999 223
7 3 456 789 999 224
2 1 456 789 999 228
2 2 466 779 999 227
3 1 456 789 999 225
3 2 466 779 999 222
3 3 456 789 999 224
4 1 456 789 990 224
NoW In my main procedure I call the procedure which returns the above resultset (REFCURSOR RESULT)for some input parameters.
[for some input values it may be more or less then 3 brances for node in refcursor resultset.]
In main proc :
Now get the node (in this example it will be 7 or 3) randomly from node_list which
have maximum brances equals to the max branch for the node returned by refcursor above.
In this case it will be 7 or 3 which has 3 branches each.Pick those rows from node_list table which have node 7 or 3 but for the entire insertion loop it should fetch column value of those rows which node=7 ( or 3..not mixure of both)
Now, replace the node for those 3 rows by node variable retunrned from fetch refcursor
and also col3 by v_col1, COL4 BY v_col2 COL5 BY V_COL3 ,COL6 (without any change)
into a new table.
But for each loop while inserting it should not pick entry from rows of node 3
if we picked node 7.
[Updated on: Wed, 27 May 2009 08:51] Report message to a moderator
|
|
|
|
Re: Do I need temporary table? [message #405390 is a reply to message #405181] |
Wed, 27 May 2009 09:07   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
In main proc :
BEGIN
dummy1(in_1,in_2,rc1);
SELECT NODE into v_node2 F
ROM node_list WHERE node in (
SELECT node
FROM (
SELECT node FROM node_list
HAVING MAX(branches)=3
/* 3 ismax value returned from refcursor for branches.
How to get last value/count for branch from refcursor */
GROUP BY node
ORDER BY DBMS_RANDOM.value)
WHERE ROWNUM = 1)
LOOP
FETCH RC1 INTO VAR1,VAR2, VAR3,VAR4,VAR5,VAR6;
EXIT WHEN RC1%NOTFOUND;
INSERT INTO TEMP_TBL
/* Inserting into a temporary table to process data further by calling some procedure in main procedure again */
SELECT V_NODE,V_BRANCH,V_LEAF,COL4,var5,COL6
FROM NODE_LIST WHERE BRANCHES=V_BRANCH AND NODE=v_node2 ;
END LOOP;
...
[Updated on: Wed, 27 May 2009 10:10] by Moderator Report message to a moderator
|
|
|
Re: Do I need temporary table? [message #405405 is a reply to message #405181] |
Wed, 27 May 2009 10:07   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
Sir, how can I replace this 3 and can use the maximum branch returned from refcursor? or is there any other way?
SELECT NODE into v_node2 FROM node_list WHERE node in (
SELECT node
FROM (
SELECT node FROM node_list
HAVING MAX(branches)=[COLOR=red]3[/COLOR]
/* 3 ismax value returned from refcursor for branches.
How to get last value/count for branch from refcursor */
GROUP BY node
ORDER BY DBMS_RANDOM.value)
WHERE ROWNUM = 1) ]
Icant use this inside loop for each fetch as it will select different set of rows as there will be selection on random node.
Please help!
[Updated on: Wed, 27 May 2009 10:12] by Moderator Report message to a moderator
|
|
|
Re: Do I need temporary table? [message #405419 is a reply to message #405388] |
Wed, 27 May 2009 10:41   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I'd love to help, if I had the first clue what you were trying to do.
What you need to do is to try and forget everything you know about the problem, and read that post as I'm reading it - as the only source of knowledge that I have about your problem.
If you could show us a sample output that you expect, then that would help.
|
|
|
Re: Do I need temporary table? [message #405423 is a reply to message #405419] |
Wed, 27 May 2009 11:04   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
Sir very thanks for the response.
SELECT NODE into v_node2 F
ROM node_list WHERE node in (
SELECT node
FROM (
SELECT node FROM node_list
HAVING MAX(branches)=3
/* 3 ismax value returned from refcursor for branches.
How to get last value/count for branch from refcursor */
GROUP BY node
ORDER BY DBMS_RANDOM.value)
WHERE ROWNUM = 1)
should give 7 or 3 [ as for this two nodes only maximum branches is 3 which is equal to
max vaue of branch returned by resultset]
LOOP
FETCH RC1 INTO VAR1,VAR2, VAR3,VAR4,VAR5,VAR6;
EXIT WHEN RC1%NOTFOUND;
INSERT INTO TEMP_TBL
/* Inserting into a temporary table to process data further by calling some procedure in main procedure again */
SELECT V_NODE,V_BRANCH,V_LEAF,COL4,var5,COL6
FROM NODE_LIST WHERE BRANCHES=V_BRANCH AND NODE=v_node2 ;
END LOOP;
REFCURSOR RESULT (from dummy1):
var1 var2 var3 var4 var5 var6
----- ---------- ---------- ---------- ---------- ----------
1 1 7 781 909 222
1 2 8 771 919 223
1 3 12 791 922 224
OUTPUT EXPECTING:
select * from temp_tbl should give (any one reulset)i.e,, 3 ROWS
NODE BRANCHES COL3 COL4 COL5 COL6
---------- ---------- ---------- ---------- ---------- ----------
7 1 7 789 909 222
7 2 8 779 919 223
7 3 12 789 922 224
or,
3 1 7 789 909 225
3 2 8 779 919 222
3 3 12 789 922 224
|
|
|
|
|
|
|
|
Re: Do I need temporary table? [message #405443 is a reply to message #405181] |
Wed, 27 May 2009 15:40   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
Using Bulk collect I tried something like this way ( This will not work but to demonstrate what I tried )
DECLARE
RC1 SYS_REFCURSOR;
TYPE T2REC IS RECORD ( V_LOC_ID VARCHAR2(5));
/* define if there are more to fetch n number of variable */
Type TYP_RFCUR2 IS TABLE of T2REC INDEX BY BINARY_INTEGER;
RECREF2 TYP_RFCUR2;
BEGIN
dummy250509 ('IND',RC1);
FETCH RC1 BULK COLLECT INTO RECREF2;
dbms_output.put_line('Count '||RECREF2.COUNT);
cntr:=RECREF2.COUNT;
put this value in the query to select random node where the max page for any node in node_list =
max page of the resultset returned
[SELECT node into v_node2
FROM (
SELECT node FROM node_list
HAVING MAX(branches)= cntr
GROUP BY node
ORDER BY DBMS_RANDOM.value)
WHERE ROWNUM = 1 ; ]
CLOSE RC1;
for i in RECREF2.FIRST..RECREF2.LAST LOOP
/* Put the insert logic */
INSERT INTO TEMP_TBL (col1,col2,..)
SELECT ( RECREF2(i).v_loc_id, col2... ) from dummy where ...;
END LOOP;
END;
/
[Updated on: Wed, 27 May 2009 18:55] Report message to a moderator
|
|
|
|
|
Re: Do I need temporary table? [message #405530 is a reply to message #405181] |
Thu, 28 May 2009 04:59   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
while trying to execute the procedure I am getting
Retrying
Retrying
Retrying
Retrying
Retrying
Retrying
Retrying
Retrying
Retrying
Retrying
Retrying
PL/SQL procedure successfully completed.
Please advice
|
|
|
|
Re: Do I need temporary table? [message #405558 is a reply to message #405443] |
Thu, 28 May 2009 06:56  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
@prachij - please try to understand. we do not know anything about the problem that you are trying to solve other than what you have told us.
We do not know your table structures
We do not know your data
We do not know what you are trying to do
We do not know the significance of a node having 3 branches
We do not know why you want a random value
we do not know what code you are running.
The reason we do not know this is because you seem incapable of telling us.
Sit down, and describe your problem in a simple way.
Tell us what bits of the problem you can't do.
You've shown us the data that your ref cursor returns, and two different sets of output data that you could expect to see - where do the values in the output data that aren't from the ref cursor come from?
I'm sorry not to be more help, but I have a day job to do, and I only have a limited amount of time. If you can make it easy for me to help you, I wil - otherwise you're on your own
|
|
|