Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2013
    Posts
    8

    Unanswered: Facing issue with the query..

    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

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Code:
    SQL> WITH my_ex_table
         AS (SELECT 'ASD0l12eds' child_id, 'ASD0l12edn' parent_id FROM DUAL UNION
      2    3           SELECT 'ASD0l58ppo', 'ASD0l16uui' FROM DUAL UNION
      4           SELECT 'ASD0l17jji', 'ASD0l19nnk' FROM DUAL UNION
      5           SELECT 'ASD0l12edm', 'ASD0l12edn' FROM DUAL UNION
      6           SELECT 'ASD0l19dds', 'ASD0l12edn' FROM DUAL UNION
      7           SELECT 'ASD0l16tdp', 'ASD0l16uui' FROM DUAL UNION
      8           SELECT 'ASD0l10kko', 'ASD0l16uui' FROM DUAL)
      9      SELECT m.child_id, m.parent_id, CONNECT_BY_ISLEAF AS no_children_exist
     10        FROM my_ex_table m
     11  START WITH parent_id = 'ASD0l16uui'
     12  CONNECT BY parent_id = PRIOR child_id
     13  /
    
    CHILD_ID                         PARENT_ID                        NO_CHILDREN_EXIST
    -------------------------------- -------------------------------- -----------------
    ASD0l10kko                       ASD0l16uui                                       1
    ASD0l16tdp                       ASD0l16uui                                       1
    ASD0l58ppo                       ASD0l16uui                                       1
    
    SQL>
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2013
    Posts
    8
    hi LKBrwn_DBA,

    Thanks for answering my question.

    But, the problem is that I will be providing the PARENT_ID during runtime to the stored procedure.

    As i don't know that how many child_records would exists for the given PARENT_ID....

    My requirement is to fetch all the child_records based on the provided PARENT_ID .... after that i want to store them in a variable ... so that i can operate on the accumulated output....




    Able to run the below query :-

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




    But Failed to run the query :-

    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 = 'L_ID'
    CONNECT BY PARENT_ID = 'L_ID' GROUP BY CHILD_ID;



    plz. help....

  4. #4
    Join Date
    Apr 2013
    Posts
    8
    plz. plz. help !!

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by acidburn007 View Post
    But, the problem is that I will be providing the PARENT_ID during runtime to the stored procedure.
    And what is the problem? just replace the 'ASD0l16uui' value with a parameter.

    Quote Originally Posted by acidburn007 View Post
    ...
    As i don't know that how many child_records would exists for the given PARENT_ID....

    My requirement is to fetch all the child_records based on the provided PARENT_ID .... after that i want to store them in a variable ... so that i can operate on the accumulated output....

    Able to run the below query :-

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

    But Failed to run the query :-

    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 = 'L_ID'
    CONNECT BY PARENT_ID = 'L_ID' GROUP BY CHILD_ID;
    The above queries fail because you did not follow the syntax I posted.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Apr 2013
    Posts
    1
    Quote Originally Posted by acidburn007 View Post
    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

    Did you try using group by ? , i hope that will provide you all the child having same parent_Id

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •