2 Replies Latest reply: Apr 18, 2013 7:57 AM by pbell RSS

    Facing issue with the query ...

    1003880
      Hi,

      I have the following data in my single table

      -------------------------------
      CHILD_ID PARENT_ID
      --------------------------------
      ASD0l12eds ASD0l12edn
      ASD0l58ppo ASD0l16uui
      ASD0l17jji ASD0l19nnk
      ASD0l12edm ASD0l12edn
      ASD0l19dds ASD0l12edn
      ASD0l16tdp ASD0l16uui
      ASD0l10kko ASD0l16uui





      -----------------
      My Requirement :-
      -----------------



      I want to fetch all the data i.e. child records based on the
      PARENT_ID provided.

      putting it in simple words, i want to iterate the table to get all the
      child records which are related to that particular PARENT_ID

      then

      need to hold that ouput in a variable and
      put a counter (which will hold total no. of rows accumulated)

      then

      will delete all the records and will set counter value to 0.



      -------------------
      Expected Result :-
      -------------------

      -------------------------------
      CHILD_ID PARENT_ID
      --------------------------------
      ASD0l12eds ASD0l12edn
      ASD0l12edm ASD0l12edn
      ASD0l19dds ASD0l12edn
      ASD0l17jji ASD0l19nnk
      ASD0l58ppo ASD0l16uui
      ASD0l16tdp ASD0l16uui
      ASD0l10kko ASD0l16uui





      ----------------
      WHAT I DID :-
      ----------------

      I built the below query and ran it:-


      SELECT M.CHILD_ID,M.PARENT_ID, CONNECT_BY_ISLEAF AS NO_CHILDREN_EXIST
      FROM MY_EX_TABLE M
      START WITH PARENT_ID = 'ASD0l16uui'
      CONNECT BY PARENT_ID = 'ASD0l16uui' ;




      --------------
      WHAT I GOT (ACTUAL RESULT) :-
      --------------


      ---------------------------------------------
      CHILD_ID PARENT_ID NO_CHILDREN_EXIST
      ----------------------------------------------
      ASD0l17jji ASD0l19nnk 0
      ASD0l17jji ASD0l19nnk 0
      ASD0l17jji ASD0l19nnk 0
      ASD0l17jji ASD0l19nnk 0
      ASD0l17jji ASD0l19nnk 0
      ASD0l17jji ASD0l19nnk 0
      .
      .
      .
      .
      .
      ASD0l17jji ASD0l19nnk 0




      NOTE:-
      NO_CHILDREN_EXIST = 0 WHEN CHILD Record is present
      NO_CHILDREN_EXIST = 1 WHEN CHILD Record is not present







      -----------
      EXPECTED RESULT :-
      -----------


      ---------------------------------------------
      CHILD_ID PARENT_ID NO_CHILDREN_EXIST
      ----------------------------------------------
      ASD0l17jji ASD0l19nnk 0





      --------
      PROBLEM :-
      --------


      1. As there are 1000 rows in the table.

      So, even if there is a single child record the query fetches 1000 rows !!!


      The Actual Result != (NOT EQUALS TO) Expected Result

      I want the exact child records corresponding to the given parent_id.

      No extra rows which i am getting currently by running the above query.

      2. Not able to hold the output of query in a variable

      i.e.




      SELECT M.CHILD_ID,M.PARENT_ID,SUM (COUNT(*)) OVER (PARTITION BY CHILD_ID),

      CONNECT_BY_ISLEAF INTO P_CHLD,P_PRNT,P_COUNT, NO_CHILDREN_EXIST
      FROM MY_EX_TABLE M
      START WITH PARENT_ID = 'ASD0l16uui'
      CONNECT BY PARENT_ID = 'ASD0l16uui' GROUP BY CHILD_ID;


      The above query fails to execute !!



      Plz. help to resolve the above 2 bottlenecks for the mentioned problem.


      Thnks

      Edited by: 1000877 on Apr 18, 2013 5:33 AM