Click here to Skip to main content
15,850,214 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I m working on table called FUT_USR_DETAILS
this table have columns child_id,Parent_id,Org_id
these columns have parent child relationship. org id is super parent.
suppose org id 1 is grand parent, parentid 1 is parent(for first time org id will be parent_id also as it creates parent id) and child id is child1
if child id1 creates user it becomes parent and it's entry goes in parentid column but for this entry it's org id column will have same value as in case when child id1 was child.
for all children, grandchildren,grand_grandchildren,so on, the org id will have same value.

i want to write a query to get level of count of hierarchy of particular user.
that is starting from grandparent(org id) how many generation levels are present for that org id .
i wrote a query to find this count. but i need to explicitly specify each rg id in query. this query does not work for entire table at time. query is

SQL
SELECT count(*)
FROM EXTDBA.fut_user_details
WHERE UD_ORG_ID ='F00761'  //here i m specifying org id explicitly 
AND UD_child_ID  IN
  (SELECT UD_PARENTID FROM EXTDBA.fut_user_details WHERE UD_ORG_ID ='F00761'
  )
AND UD_child_ID!=UD_ORG_ID;

programmatically if i want to do this i'll do it like this.
select distinct org id from table. take it in array
anf for each item in array ie for each org id i'll run for llop
like

SQL
for(i=0;i<arrorgid.count;i++)>
{
SELECT count(*)
FROM EXTDBA.fut_user_details
WHERE UD_ORG_ID =arrOrgId[i]  //here i m specifying org id explicitly 
AND UD_child_ID  IN
  (SELECT UD_PARENTID FROM EXTDBA.fut_user_details WHERE UD_ORG_ID =arrOrgId[i]
  )
AND UD_child_ID!=UD_ORG_ID;

}
but i m asked to do it by quering database directly.
can someone tell me how to do so
Posted
Updated 30-Nov-11 3:40am
v2

I don't get the point with the orgid being the parentid for the first level. You're mixing up users with organisations.
Let the orgid define the organisational unit or department and so on, and use a normal id/parentid schema where the toplevel has <null> as the parentid.

Anyway, try this query:
SQL
SELECT  UD_child_ID,UD_PARENTID,LEVEL
FROM    EXTDBA.fut_user_details
CONNECT BY PRIOR UD_child_ID = UD_PARENTID
START WITH UD_PARENTID = 'F00761'
 
Share this answer
 
There are a few ways you can do this:

0) Pass the ID's to a stored procedure in a comma-delimited string, and in that stored proc, parse the string of IDs into a temporary table, and then perform a query using the temp table together with your actual data table.

1) Perform a series of queries, one for each ID, and merge the retruned datasets together in the code that called the stored proc

2) Create a stored proc that accepts a limted number of ID parameters (integers I suspect), and performs a query using those IDs, and call this stored proc as many times as necessary, merging the returned datasets in your code.

Personally, I'd go with the first option.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900