Home » SQL & PL/SQL » SQL & PL/SQL » Need help w/ cursors
Need help w/ cursors [message #242382] Fri, 01 June 2007 14:50 Go to next message
sasrs99
Messages: 3
Registered: March 2007
Junior Member
I am looking for help with the following. Using the 1st cursor below, I need to output every possible combination of ITEM/LOC that does not include the first location #.

Using this...

SKU.ITEM SKU.LOC
12345 LOC1
12345 LOC2
12345 LOC3

Create this...
ITEM SRCLOC DESTLOC
12345 LOC1 LOC2
12345 LOC1 LOC3

12345 LOC2 LOC1
12345 LOC2 LOC3

12345 LOC3 LOC1
12345 LOC3 LOC2

I eventually need to use these values to insert some records into a table, but I first want to make sure all the combinations are correct.


SET serveroutput ON SIZE 1000000
SET echo ON;
SET TIME ON
SET autotrace ON

WHENEVER SQLERROR CONTINUE NONE;

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;

DECLARE
v_i INT := 0;
v_b INT := 0;
v_c NUMBER;
v_count NUMBER;

CURSOR c1 IS
SELECT DISTINCT ITEM, LOC FROM SKU;

CURSOR c2 IS
SELECT ITEM, LOC FROM SKU WHERE ITEM = c1_rec.ITEM AND LOC <> c1_rec.LOC;

BEGIN
DBMS_OUTPUT.ENABLE(1000000);

FOR c1_rec IN c1 LOOP

FOR c2_rec IN c2 LOOP

DBMS_OUTPUT.PUT_LINE ('ITEM: '||c1_rec.ITEM||' LOC:'||c1_rec.ITEM);


END LOOP;
END LOOP;
END;
/

EXIT;

Re: Need help w/ cursors [message #242384 is a reply to message #242382] Fri, 01 June 2007 15:01 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
PLEASE follow posting guidelines as stated in the #1 STICKY post at top of forum.
>I need to output every possible combination of ITEM/LOC that does not include the first location #.
FIRST is an oxymoron with regards to a collection of records.
Re: Need help w/ cursors [message #242385 is a reply to message #242384] Fri, 01 June 2007 15:03 Go to previous messageGo to next message
sasrs99
Messages: 3
Registered: March 2007
Junior Member
I'm thinking I need to create a single query that will return all possible combinations, but I don't know how.
Re: Need help w/ cursors [message #242390 is a reply to message #242385] Fri, 01 June 2007 15:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select a.item, a.loc, b.loc
from sku a, sku b
where a.item = b.item and a.loc != b.loc
/

Regards
Michel
Re: Need help w/ cursors [message #242391 is a reply to message #242382] Fri, 01 June 2007 15:17 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
How is this problem different than this problem?
http://www.dbforums.com/showthread.php?t=1618925
Re: Need help w/ cursors [message #242392 is a reply to message #242391] Fri, 01 June 2007 15:23 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Seems not very different as well as the answer. Smile
Very weird! ./fa/1986/0/

Michel
Previous Topic: show all recs from where clause
Next Topic: multipole row return function - this is definitely NOT urgent, so answer me in the next few weeks
Goto Forum:
  


Current Time: Tue Dec 06 02:54:48 CST 2016

Total time taken to generate the page: 0.11827 seconds