0 Replies Latest reply: Apr 19, 2013 12:36 AM by 1003880 RSS

    Facing issue with 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.