Home » SQL & PL/SQL » SQL & PL/SQL » Connect by not looping
Connect by not looping [message #359679] Tue, 18 November 2008 00:14 Go to next message
tarmenel@gmail.com
Messages: 18
Registered: November 2008
Junior Member
Hi there,
I'm sure I'm just not seeing something but after half a day I can't seem to find out how to loop with the Connect By clause.
I have a table that has the parent to the next row in one of the attribute columns. I would like to start with a certain column and then work my way down the tree.
SELECT wci.container_item_id,
       wci.attribute1,
       wci.load_item_id,
       wci.attribute2,
       LEVEL
FROM   wsh_container_items wci
START  WITH wci.attribute2 = 'Y' 
CONNECT BY PRIOR wci.container_item_id = wci.attribute1;

I can see the attribute1 and when I query this table just with the value then I get the row that I'm trying to traverse to but the Connect By doesn't seem to want to traverse. Can anyone point out what I'm doing incorrectly.
Re: Connect by not looping [message #359695 is a reply to message #359679] Tue, 18 November 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements along with the result you want with these data.

Post what you tried and why it does not work.

Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Connect by not looping [message #359727 is a reply to message #359695] Tue, 18 November 2008 01:42 Go to previous messageGo to next message
tarmenel@gmail.com
Messages: 18
Registered: November 2008
Junior Member
----------------------------------------
Database Server
----------------------------------------
RDBMS : 10.2.0.2.0
Oracle Applications : 11.5.10.2
----------------------------------------

Table creation will be a bit difficult as this is one of the table from the ERP system. This is the describe of the table:

MASTER_LOCATION_ID NUMBER
MASTER_ORGANIZATION_ID NUMBER
CONTAINER_ITEM_ID NUMBER
LOAD_ITEM_ID NUMBER
MAX_LOAD_QUANTITY NUMBER
ATTRIBUTE_CATEGORY VARCHAR2(30) Y
ATTRIBUTE1 VARCHAR2(150) Y
ATTRIBUTE2 VARCHAR2(150) Y
ATTRIBUTE3 VARCHAR2(150) Y
ATTRIBUTE4 VARCHAR2(150) Y
ATTRIBUTE5 VARCHAR2(150) Y
ATTRIBUTE6 VARCHAR2(150) Y
ATTRIBUTE7 VARCHAR2(150) Y
ATTRIBUTE8 VARCHAR2(150) Y
ATTRIBUTE9 VARCHAR2(150) Y
ATTRIBUTE10 VARCHAR2(150) Y
ATTRIBUTE11 VARCHAR2(150) Y
ATTRIBUTE12 VARCHAR2(150) Y
ATTRIBUTE13 VARCHAR2(150) Y
ATTRIBUTE14 VARCHAR2(150) Y
ATTRIBUTE15 VARCHAR2(150) Y
CREATION_DATE DATE
CREATED_BY NUMBER
LAST_UPDATE_DATE DATE
LAST_UPDATED_BY NUMBER
LAST_UPDATE_LOGIN NUMBER Y
PROGRAM_APPLICATION_ID NUMBER Y
PROGRAM_ID NUMBER Y
PROGRAM_UPDATE_DATE DATE Y
REQUEST_ID NUMBER Y
PREFERRED_FLAG VARCHAR2(1) Y

I've tried making to_number(attribute1), placing where parameters, moving them to the start with section and leaving out the prior. I can't understand why it doe snot want to continue in the loop. Here is a sample of the info I am looking to traverse:
row,container_item_id,attribute1,load_item_id,attribute2
1 476215 2031 8794 Y
2 2031 2033 8794
3 2032 8794
4 2033 8794
5 2034 8794
6 2036 8794
7 2037 8794
8 2038 8794
9 31029 8794
10 667004 8794
11 667005 8794
12 2028 8794

SELECT wci.container_item_id, wci.attribute1, wci.load_item_id, wci.attribute2
FROM   wsh_container_items wci
WHERE  wci.load_item_id = 8794
AND    wci.master_organization_id = 104;

where attribute1 indicates the child and attribute2 indicates the highest level
Re: Connect by not looping [message #359732 is a reply to message #359727] Tue, 18 November 2008 01:51 Go to previous messageGo to next message
tarmenel@gmail.com
Messages: 18
Registered: November 2008
Junior Member
SELECT wci.container_item_id,
       wci.attribute1,
       wci.load_item_id,
       wci.attribute2,
       LEVEL
FROM   wsh_container_items wci
WHERE  wci.load_item_id = 8794
AND    wci.master_organization_id = 104
START  WITH wci.container_item_id=2033--wci.attribute2 = 'Y' 
and wci.load_item_id = 8794
AND    wci.master_organization_id = 104
CONNECT BY PRIOR wci.container_item_id = wci.attribute1;

When I use the above statement it brings back the three rows I want to see but this means I'm stating from the bottom and working up, not what I'm expecting or what I want!?!?
Re: Connect by not looping [message #359953 is a reply to message #359732] Tue, 18 November 2008 23:28 Go to previous messageGo to next message
tarmenel@gmail.com
Messages: 18
Registered: November 2008
Junior Member
Does any one have an idea why the connect by would work from the child node up instead of working down the tree?
Re: Connect by not looping [message #359971 is a reply to message #359953] Wed, 19 November 2008 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We still have no test case.

Regards
Michel
Re: Connect by not looping [message #359985 is a reply to message #359971] Wed, 19 November 2008 02:25 Go to previous messageGo to next message
tarmenel@gmail.com
Messages: 18
Registered: November 2008
Junior Member
-- Create table
create table SCOTT.SCOTT_CONTAINER_ITEMS
(
  MASTER_LOCATION_ID     NUMBER not null,
  MASTER_ORGANIZATION_ID NUMBER not null,
  CONTAINER_ITEM_ID      NUMBER not null,
  LOAD_ITEM_ID           NUMBER not null,
  MAX_LOAD_QUANTITY      NUMBER not null,
  ATTRIBUTE_CATEGORY     VARCHAR2(30),
  ATTRIBUTE1             VARCHAR2(150),
  ATTRIBUTE2             VARCHAR2(150),
  ATTRIBUTE3             VARCHAR2(150),
  ATTRIBUTE4             VARCHAR2(150),
  ATTRIBUTE5             VARCHAR2(150),
  ATTRIBUTE6             VARCHAR2(150),
  ATTRIBUTE7             VARCHAR2(150),
  ATTRIBUTE8             VARCHAR2(150),
  ATTRIBUTE9             VARCHAR2(150),
  ATTRIBUTE10            VARCHAR2(150),
  ATTRIBUTE11            VARCHAR2(150),
  ATTRIBUTE12            VARCHAR2(150),
  ATTRIBUTE13            VARCHAR2(150),
  ATTRIBUTE14            VARCHAR2(150),
  ATTRIBUTE15            VARCHAR2(150),
  CREATION_DATE          DATE not null,
  CREATED_BY             NUMBER not null,
  LAST_UPDATE_DATE       DATE not null,
  LAST_UPDATED_BY        NUMBER not null,
  LAST_UPDATE_LOGIN      NUMBER,
  PROGRAM_APPLICATION_ID NUMBER,
  PROGRAM_ID             NUMBER,
  PROGRAM_UPDATE_DATE    DATE,
  REQUEST_ID             NUMBER,
  PREFERRED_FLAG         VARCHAR2(1)
)
;
-- Create/Recreate indexes 
create index SCOTT.SCOTT_CONTAINER_ITEMS_N1 on SCOTT.SCOTT_CONTAINER_ITEMS (CONTAINER_ITEM_ID)
  tablespace APPS_TS_TX_IDX
  pctfree 10
  initrans 11
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create index SCOTT.SCOTT_CONTAINER_ITEMS_N2 on SCOTT.SCOTT_CONTAINER_ITEMS (LOAD_ITEM_ID)
  tablespace APPS_TS_TX_IDX
  pctfree 10
  initrans 11
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create unique index SCOTT.SCOTT_CONTAINER_ITEMS_U1 on SCOTT.SCOTT_CONTAINER_ITEMS (CONTAINER_ITEM_ID, MASTER_ORGANIZATION_ID, LOAD_ITEM_ID)
  tablespace APPS_TS_TX_IDX
  pctfree 10
  initrans 11
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );

INSERT INTO scott.SCOTT_container_items wci VALUES(-1,104,476215,8794,10,NULL,'2031','Y','5 TEST','361214',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,SYSDATE,1536,SYSDATE,1111,13815230,NULL,NULL,NULL,NULL,'N');

INSERT INTO scott.SCOTT_container_items wci VALUES(-1,104,2031,8794,250,NULL,'2033',NULL,'6 TEST','6843',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,SYSDATE,1536,SYSDATE,1111,13815230,NULL,NULL,NULL,NULL,'Y');

INSERT INTO scott.SCOTT_container_items wci VALUES(-1,104,2032,8794,1000,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,SYSDATE,1536,SYSDATE,1536,116830,NULL,NULL,NULL,NULL,'N');

INSERT INTO scott.SCOTT_container_items wci VALUES(-1,104,2033,8794,2000,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,SYSDATE,1536,SYSDATE,1536,116830,NULL,NULL,NULL,NULL,'N');

INSERT INTO scott.SCOTT_container_items wci VALUES(-1,104,2034,8794,3000,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,SYSDATE,1536,SYSDATE,1536,116830,NULL,NULL,NULL,NULL,'N');

INSERT INTO scott.SCOTT_container_items wci VALUES(-1,104,2036,8794,750,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,SYSDATE,1536,SYSDATE,1536,116830,NULL,NULL,NULL,NULL,'N');

INSERT INTO scott.SCOTT_container_items wci VALUES(-1,104,2037,8794,1500,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,SYSDATE,1536,SYSDATE,1536,116830,NULL,NULL,NULL,NULL,'N');

INSERT INTO scott.SCOTT_container_items wci VALUES(-1,104,2038,8794,2250,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,SYSDATE,1536,SYSDATE,1536,116830,NULL,NULL,NULL,NULL,'N');

INSERT INTO scott.SCOTT_container_items wci VALUES(-1,104,31029,8794,125,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,SYSDATE,1536,SYSDATE,1536,116830,NULL,NULL,NULL,NULL,'N');

INSERT INTO scott.SCOTT_container_items wci VALUES(-1,104,667004,8794,220,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,SYSDATE,1536,SYSDATE,1536,116830,NULL,NULL,NULL,NULL,'N');

INSERT INTO scott.SCOTT_container_items wci VALUES(-1,104,667005,8794,55,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,SYSDATE,1536,SYSDATE,1536,116830,NULL,NULL,NULL,NULL,'N');

INSERT INTO scott.SCOTT_container_items wci VALUES(-1,104,2028,8794,2160,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,SYSDATE,1536,SYSDATE,1536,116830,NULL,NULL,NULL,NULL,'N');

Re: Connect by not looping [message #360040 is a reply to message #359985] Wed, 19 November 2008 05:46 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
The queries seem (to me) to give the proper results
SELECT wci.container_item_id,
       wci.attribute1,
       wci.load_item_id,
       wci.attribute2,
       LEVEL
FROM    SCOTT_CONTAINER_ITEMS wci
START  WITH wci.attribute2 = 'Y' 
CONNECT BY PRIOR wci.container_item_id =  wci.attribute1;


CONTAINER_ITEM_ID	ATTRIBUTE1	LOAD_ITEM_ID ATTRIBUTE2	LEVEL
476215	                2031	        8794	     Y        1

SELECT wci.container_item_id,
       wci.attribute1,
       wci.load_item_id,
       wci.attribute2,
       LEVEL
FROM    SCOTT_CONTAINER_ITEMS wci
START  WITH wci.attribute2 = 'Y' 
CONNECT BY  wci.container_item_id =  PRIOR wci.attribute1;

CONTAINER_ITEM_ID	ATTRIBUTE1	LOAD_ITEM_ID ATTRIBUTE2 LEVEL
476215	                2031	        8794	      Y	      1
2031	                2033	        8794		      2
2033		                        8794		      3

[Updated on: Wed, 19 November 2008 05:47]

Report message to a moderator

Re: Connect by not looping [message #360250 is a reply to message #360040] Thu, 20 November 2008 03:32 Go to previous messageGo to next message
tarmenel@gmail.com
Messages: 18
Registered: November 2008
Junior Member
I'm only getting one row Confused
I also thought the last query would return what I was looking for but it returns only one row. Is there something in the database setup that could be causing this?
Re: Connect by not looping [message #360253 is a reply to message #360250] Thu, 20 November 2008 03:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
what version of Oracle are you using?
Re: Connect by not looping [message #360257 is a reply to message #360253] Thu, 20 November 2008 03:52 Go to previous message
tarmenel@gmail.com
Messages: 18
Registered: November 2008
Junior Member
----------------------------------------
Database Server
----------------------------------------
RDBMS : 10.2.0.2.0
Oracle Applications : 11.5.10.2
----------------------------------------
Previous Topic: How to load a .raw file using a procedure??
Next Topic: SQL to find out OF responsibilities
Goto Forum:
  


Current Time: Thu Dec 08 08:28:56 CST 2016

Total time taken to generate the page: 0.26978 seconds