Hello, folks.

Well, I can't get my head around that particular function.

I want to calculate and then return the total amount of hours assigned for a given stage_no.

I got confused. Do I have to use COUNT(*)?

``````CREATE OR REPLACE FUNCTION total_numbers_hours(hours NUMBER, stage_no NUMBER) RETURN NUMBER IS

return_val NUMBER;

BEGIN

SELECT COUNT(*)
INTO return_val
FROM employees a, stage s
WHERE  s.stage_id = t.stage_id
AND estimated_hours = hours
AND stage_id = stage_no

RETURN return_val;

END;``````

And this is my statement:

``````SELECT s.stage_id, t.task_id, a.employee_no, total_numbers_hours(a.estimated_hours, s.stage_id)
FROM   stage s, task t, employees a
WHERE  s.stage_id = t.stage_id

I am working on it now, so I will update it, as I was typing it I figured out that I messed it up completely. I will be glad if any of you stop and just throw me some thoughts or something. Thank you, guys.

P.S. Oh, yeah, just wanted to ask about my joins, if I did them right.

I am trying to connect 3 tables

``````STAGE table
Stage_id(primary key)
stage_no``````
``````Task table
``````Employees table
employees_no(primary)
estimated_hours``````

This is what I did so far...
Still I can enter a value but it doesn't work...

``````CREATE OR REPLACE FUNCTION total_numbers_hours(hours employees.estimated_hours%TYPE) RETURN NUMBER IS

return_val NUMBER;

BEGIN

SELECT COUNT(*)
INTO return_val
FROM employees a, stage s
WHERE  s.stage_id = t.stage_id
AND estimated_hours = hours

RETURN return_val;

END;
/``````

And this is my statement:

``````SELECT estimated hours, total_numbers_hours(estimated_hours)
FROM   employees
WHERE  UPPER(estimated_hours)like UPPER('&employees_estimated_hours%');``````

SOLVED #

If you have to calculate the total number of hours, use the function SUM with group by.

Yeah did long time ago, completely redone it. Cheers anyway.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.