This discussion is archived
2 Replies Latest reply: Apr 18, 2013 5:57 AM by pbell RSS

Facing issue with the query ...

1003880 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points