0

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    t.task_id  = a.task_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
AND    t.task_id  = a.task_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
task_id(primary key)
task_no
Employees table
employees_no(primary)
task_id
estimated_hours

Edited by SQLpower: n/a

2
Contributors
4
Replies
5
Views
5 Years
Discussion Span
Last Post by SQLpower
0

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    t.task_id  = a.task_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%');
0

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

0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.