Home » SQL & PL/SQL » SQL & PL/SQL » Do I need temporary table? (Oracle 9.2.0.3)
Do I need temporary table? [message #405181] Tue, 26 May 2009 14:51 Go to next message
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 Go to previous messageGo to next message
pablolee
Messages: 2834
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 Go to previous messageGo to next message
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 #405379 is a reply to message #405374] Wed, 27 May 2009 08:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
When we say 'formatted' we include such trivial details as actually getting your numbers into columns, and under the correct column heading.
Re: Do I need temporary table? [message #405385 is a reply to message #405181] Wed, 27 May 2009 08:42 Go to previous messageGo to next message
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 :
 dummy1(in_1,in_2,rc1);







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 #405388 is a reply to message #405181] Wed, 27 May 2009 08:52 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir, I have formatted the text ...I need your help here.
Re: Do I need temporary table? [message #405390 is a reply to message #405181] Wed, 27 May 2009 09:07 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #405426 is a reply to message #405181] Wed, 27 May 2009 11:57 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir, is there a way?
Re: Do I need temporary table? [message #405433 is a reply to message #405181] Wed, 27 May 2009 13:44 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir I provided the expected output...could you please provide your valuable suggesion? thanks
Re: Do I need temporary table? [message #405436 is a reply to message #405181] Wed, 27 May 2009 14:36 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir/Madam,
Is it possible to do anyway? is there any way?
Re: Do I need temporary table? [message #405438 is a reply to message #405436] Wed, 27 May 2009 15:05 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
If you want such a quick response, pay a consultant. You are not paying anyone here as far as I know, so they do not owe you anything in a reasonable amount of time.
Re: Do I need temporary table? [message #405440 is a reply to message #405438] Wed, 27 May 2009 15:14 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir, I thought its so easier for you to give some alternative way...my apologies!
Re: Do I need temporary table? [message #405443 is a reply to message #405181] Wed, 27 May 2009 15:40 Go to previous messageGo to next message
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 #405449 is a reply to message #405181] Wed, 27 May 2009 21:12 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Sir, I thought its so easier for you to give some alternative way.
Nothing is impossible for the person who does not have to actually do it.
Re: Do I need temporary table? [message #405519 is a reply to message #405449] Thu, 28 May 2009 03:43 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thank you all for giving your time ...Sir want to know if there is other alternatives other than this...


[Updated on: Thu, 28 May 2009 03:52]

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 Go to previous messageGo to next message
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 #405538 is a reply to message #405181] Thu, 28 May 2009 05:11 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
None of the code you've posted so far contains anything that would do that - so how are we supposed to know what's going on?
Re: Do I need temporary table? [message #405558 is a reply to message #405443] Thu, 28 May 2009 06:56 Go to previous message
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
Previous Topic: Procedure failing-Need to know where?
Next Topic: Rownum Within Group By
Goto Forum:
  


Current Time: Sat Dec 03 08:05:27 CST 2016

Total time taken to generate the page: 0.05980 seconds