OraFAQ Forum: SQL & PL/SQL » Parent child count?

Home » SQL & PL/SQL » SQL & PL/SQL » Parent child count?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Parent child count? [message #653620]

Tue, 12 July 2016 13:53

P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad

Member

Hi Friends,
I have a scenario where i need to count Number of logged hours against tasks in projects
Where project contains Number of sub projects and Each Sub project contains n number of Tasks.i have tried like this but it is getting for only one project.

CREATE OR REPLACE FUNCTION im_project_total_bh_log_hours(integer,character)
  RETURNS character varying AS
$BODY$
DECLARE
	p_project_id alias for $1;
	p_hours_type alias for $2;		
	v_name	varchar;
	v_nb_name varchar;
	p_parent_id varchar;


BEGIN
SELECT PARENT_ID 
into p_parent_id
FROM 
IM_PROJECTS
WHERE 
PROJECT_ID = p_project_id;
IF p_parent_id IS NULL THEN
SELECT
sum(hours),sum(non_billable_hours) into  v_name,v_nb_name
FROM 
im_hours
where 
project_id in (SELECT PROJECT_ID FROM IM_PROJECTS WHERE PARENT_ID= p_project_id);
IF v_name IS NULL THEN
return 0.00;
ELSE 
return v_name;
END IF;
ELSE
SELECT SUM(HOURS) 
into v_name
from im_hours
where project_id= p_project_id;
return v_name;
END IF;
end;

Report message to a moderator

Re: Parent child count? [message #653621 is a reply to message #653620]

Tue, 12 July 2016 13:56

P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad

Member

I have one main project in-turn which contians 3 sub projects and each sub-project contains multiple tasks...

Report message to a moderator

Re: Parent child count? [message #653633 is a reply to message #653621]

Tue, 12 July 2016 15:12

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Why didn't you feedback in your previous SQL topic?

Do you
REALLY
think
your code
is
formatted?

[Updated on: Tue, 12 July 2016 15:12]

Report message to a moderator

Re: Parent child count? [message #653634 is a reply to message #653633]

Tue, 12 July 2016 15:35

P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad

Member

I got the solution and it is working as expected...
Thanq Guys

Report message to a moderator

Re: Parent child count? [message #653635 is a reply to message #653634]

Tue, 12 July 2016 15:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

thiruvari wrote on Tue, 12 July 2016 13:35

I got the solution and it is working as expected...
Thanq Guys

Posting Guideline say that you should post solution to assist others that may have same problem in the future.

Report message to a moderator

Re: Parent child count? [message #653636 is a reply to message #653635]

Tue, 12 July 2016 16:22

P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad

Member

Here you Go friends..

CREATE OR REPLACE FUNCTION im_project_total_bh_log_hours(integer,character)
  RETURNS character varying AS
$BODY$
DECLARE
	p_project_id alias for $1;
	p_hours_type alias for $2;		
	v_name	varchar;
	v_nb_name varchar;
	p_parent_id varchar;

BEGIN
SELECT PARENT_ID 
into p_parent_id
FROM 
IM_PROJECTS
WHERE 
PROJECT_ID = p_project_id;
IF p_parent_id IS NULL THEN
SELECT
sum(hours),sum(non_billable_hours) into  v_name,v_nb_name
FROM 
im_hours
where 
project_id in (SELECT PROJECT_ID FROM IM_PROJECTS WHERE PARENT_ID = p_project_id);
   IF v_name is NULL THEN 
	SELECT
	sum(hours),sum(non_billable_hours) into  v_name,v_nb_name
	FROM 
	im_hours
	where 
	project_id in (SELECT PROJECT_ID FROM IM_PROJECTS WHERE PARENT_ID in (SELECT PROJECT_ID FROM IM_PROJECTS WHERE PARENT_ID = p_project_id)); 
   END IF;
IF v_name IS NULL THEN
return 0.00;
ELSE 
return v_name;
END IF;
ELSE
SELECT SUM(HOURS) 
into v_name
from im_hours
where project_id= p_project_id;
return v_name;
END IF;
end;$BODY$

Report message to a moderator

Re: Parent child count? [message #653647 is a reply to message #653636]

Wed, 13 July 2016 03:36

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

That's not indented properly at all.
More importantly it's not an oracle function.

Report message to a moderator

Previous Topic:	PLS-00907: cannot load libraryunit PROD.DAILY_RPT (referenced by )
Next Topic:	ORA-01722 when returning ROWTYPE in function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 04:38:02 CDT 2024