Home » SQL & PL/SQL » SQL & PL/SQL » Still not getting it ~ Please help
icon9.gif  Still not getting it ~ Please help [message #186362] Mon, 07 August 2006 13:00 Go to next message
anxusnewbie
Messages: 4
Registered: August 2006
Junior Member
set linesize 2000;
set serveroutput on;

DECLARE

v_facility_seq facility.facility_seq%TYPE;

CURSOR c_facility
IS
SELECT facility_seq from facility f
where f.facility_seq IN
('117529','146947','146962','147175','147377','147451','147509');
BEGIN

OPEN c_facility;
dbms_Output.ENABLE(1000000);
LOOP
FETCH c_facility INTO v_facility_seq;
EXIT WHEN c_facility%NOTFOUND;

BEGIN
SELECT DISTINCT FACILITY_SEQ
FROM SALES_DATA S, facility f
WHERE S.FACILITY_SEQ in v_facility_seq


dbms_Output.Put_Line(v_facility_seq);

END;
END LOOP;
CLOSE c_facility;
END;
Re: Still not getting it ~ Please help [message #186366 is a reply to message #186362] Mon, 07 August 2006 13:40 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
What are you trying to do here?
Re: Still not getting it ~ Please help [message #186372 is a reply to message #186366] Mon, 07 August 2006 13:57 Go to previous messageGo to next message
anxusnewbie
Messages: 4
Registered: August 2006
Junior Member
I want to get

1. Report of sales_data.facility_seq that match the facility_seq from the cursor.

2. Then I want to use those values only from the cursor and match them with the group table that has facility_seqs.

3. Lastly, delete the facility_seqs that was captured in the cursor that are in the facility table.

I hope I am on the right track.
Re: Still not getting it ~ Please help [message #186476 is a reply to message #186372] Tue, 08 August 2006 02:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, now you've told us what you're trying to do, could we impose upon you to tell us what the problem that you're getting is?
Re: Still not getting it ~ Please help [message #186543 is a reply to message #186362] Tue, 08 August 2006 07:29 Go to previous messageGo to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Hi ... Can you try with this code.. I dont have the specifications of the table which you have...



DECLARE
v_facility_seq facility.facility_seq%TYPE;
TYPE A_TYPE IS TABLE OF SALES_DATA. FACILITY_SEQ%TYPE INDEX BY PLS_INTEGER;
MY_TYPE A_TYPE;

CURSOR c_facility
IS
SELECT facility_seq from facility f
where f.facility_seq IN
('117529','146947','146962','147175','147377','147451','147509');
BEGIN

OPEN c_facility;
dbms_Output.ENABLE(1000000);
LOOP
FETCH c_facility INTO v_facility_seq;
EXIT WHEN c_facility%NOTFOUND;

BEGIN

SELECT DISTINCT FACILITY_SEQ BULK COLLECT INTO MY_TYPE
FROM SALES_DATA S, facility f
WHERE S.FACILITY_SEQ in v_facility_seq

FOR I_LOOP IN MY_TYPE.FIRST.. MY_TYPE.LAST LOOP


--YOUR DELETE CODE GOES HERE WITH
MY_TYPE(I_LOOP)

END LOOP;

dbms_Output.Put_Line(v_facility_seq);

END;
END LOOP;
CLOSE c_facility;
END;
Re: Still not getting it ~ Please help [message #186551 is a reply to message #186362] Tue, 08 August 2006 08:12 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
What are you trying to do with the select

SELECT DISTINCT FACILITY_SEQ
FROM SALES_DATA S, facility f
WHERE S.FACILITY_SEQ in v_facility_seq

You are generating a Cartesian join. Why is the SALES_DATA and FACILITY tables not joined in the where clause?
Re: Still not getting it ~ Please help [message #186582 is a reply to message #186476] Tue, 08 August 2006 10:36 Go to previous message
anxusnewbie
Messages: 4
Registered: August 2006
Junior Member
My goal is to get all the facilities with sales data from a group of given facility_seqs. If the facility has sales data then produce report but do not delete them.

From that list delete the facilities from two other tables that are also linked by facility_seq.


My thought was to create a cursor that captured the facility_seq that were in that given list. From that select only those with sales data and produce report. And from that delete the facility_seq that are in the facility table and a group table.
Previous Topic: object types in oracle.
Next Topic: Exception handling
Goto Forum:
  


Current Time: Wed Dec 07 14:58:43 CST 2016

Total time taken to generate the page: 0.07512 seconds