Search code examples
sqloracle-databasefunctionisql

Oracle iSQL function issue

EDIT: i have changed my code now.

I have a problem with my function i get no errors when creating the function and i can run a basic version of the function i made with no problems but when i call the entire function i get blank rows.

this version works 

SELECT   SUM(t.estimated_hours*c.hrly_charge_rate)
FROM     task t, task_history ta, task_type tt, charge_grade c
WHERE    t.task_id = ta.task_id
AND      tt.task_type_no = t.task_type_no
AND      c.grade_no = tt.grade_no
AND      ta.task_history_id = 1;

but this does not work the rows are just blank ???

SHOW ERRORS 

CREATE OR REPLACE FUNCTION total_cost (tn task.task_id%TYPE) 
RETURN NUMBER IS

    cost_var   NUMBER;

BEGIN
    SELECT   SUM(t.estimated_hours*c.hrly_charge_rate)
    INTO     cost_var
    FROM     task t, task_history ta, task_type tt, charge_grade c
    WHERE    t.task_id = ta.task_id
    AND      tt.task_type_no = t.task_type_no
    AND      c.grade_no = tt.grade_no
    AND      t.task_type_no = tn
    AND      ta.task_history_id = 1;

    RETURN   cost_var;

END;
/

-- my call select statment 


SELECT  total_cost(t.task_id) AS task_cost
FROM    task t;

it runs with no errors but the rows are all blank ? Can any show me what i did wrong please.

here Is the Entity Relationship Diagram (ERD) ERD AND QUESTION /* (b) to calculate and return the total cost using standard rates of a given task at the date of the estimate (ie before any further changes to estimated hours were made. Code scripts, including SELECT statements to test each function + ‘screenshot’ of the output from each test statement. */

Solution

  • It seems that you are using an HAVING to apply a simple WHERE condition. Same examples:

    SQL> create table groupTable (id number, num number);

Table created.

SQL> select sum(num)
  2  from groupTable
  3  where id = 1;

  SUM(NUM)
----------


SQL> select sum(num)
  2  from groupTable
  3  having id = 1;
having id = 1
       *
ERROR at line 3:
ORA-00979: not a GROUP BY expression


SQL> select sum(num)
  2  from groupTable
  3  group by id
  4  having id = 1;

no rows selected

SQL> select sum(num)
  2  from groupTable
  3  group by id
  4  having sum(num) = 1;

no rows selected

SQL> select sum(num)
  2  from groupTable
  3  where sum(num) = 1
  4  group by id;
where sum(num) = 1
      *
ERROR at line 3:
ORA-00934: group function is not allowed here


SQL>