Member Avatar for Venus_3

i have this code..,. i dont see what's wrong why its only working on the first record of LOOP of X

set serveroutput on size 30000;

DECLARE

VTEMP NUMBER;
VPARENT NUMBER;

BEGIN

FOR X IN (
  SELECT TEMPLATE_ID, TYPE_ID, TYPE_KIND, RECORD_ID FROM RT_RECORDS
  WHERE TEMPLATE_ID = 106 

)LOOP
DBMS_OUTPUT.PUT_LINE('TYPE_ID X :' || X.TYPE_ID );

FOR Y IN (

SELECT OBJECT_ID, OBJECT_TYPE CHILD_TYPE, LEVEL,
       PARENT_ID,  RPA_N.GETTYPEID(PARENT_ID)PARENT_TYPE
                    FROM NODES N          
                    CONNECT BY PRIOR  N.OBJECT_ID = N.PARENT_ID
                    START WITH N.OBJECT_ID  =  18464078 ) LOOP

          DBMS_OUTPUT.PUT_LINE('TYPE_ID :' || X.TYPE_ID  || ' PARENT_ID: ' || Y.PARENT_TYPE );

          IF X.TYPE_ID = Y.PARENT_TYPE THEN
          -- CHILD
            SELECT RECORD_ID INTO VTEMP  FROM RT_RECORDS WHERE TEMPLATE_ID = 106
            AND TYPE_ID = Y.CHILD_TYPE;
      -- PARENT            
            SELECT RECORD_ID INTO VPARENT FROM RT_RECORDS WHERE TEMPLATE_ID = 106
            AND TYPE_ID = Y.PARENT_TYPE;

            DBMS_OUTPUT.PUT_LINE('RECORD_ID :' || VTEMP || ' PARENT_ID: ' || VPARENT );          
          END IF;

    END LOOP;

END LOOP;

  EXCEPTION
          WHEN OTHERS THEN
          NULL;

END;

upon running this will be the output

TYPE_ID X :2
TYPE_ID :2 PARENT_ID: 281
TYPE_ID :2 PARENT_ID: 2
RECORD_ID :29098 PARENT_ID: 29097
TYPE_ID :2 PARENT_ID: 2324
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 45
TYPE_ID :2 PARENT_ID: 2324
TYPE_ID :2 PARENT_ID: 2324
TYPE_ID :2 PARENT_ID: 2324
TYPE_ID :2 PARENT_ID: 2324
TYPE_ID :2 PARENT_ID: 2324
TYPE_ID :2 PARENT_ID: 2

LOOP X has many records in it.. but it only loops on the first record

Edited by Venus_3
  • 2 Contributors
  • 1 Reply
  • 282 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by AndrisP
Member Avatar for AndrisP

How mutch TEMPLATE_ID = 106 is in your subquery?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.