Oracle iSQL功能问题(Oracle iSQL function issue)

Oracle iSQL功能问题(Oracle iSQL function issue)

编辑：我现在改变了我的代码。

我的功能有问题我在创建函数时没有错误，我可以运行我所做的功能的基本版本没有问题但是当我调用整个函数时，我得到空行。

这个版本有效

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;

但这不起作用行只是空白???

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;

它运行没有错误，但行都是空白的？ 可以告诉我我做错了吗。

这是实体关系图（ERD） AND QUESTION / *（b）使用估计日期的给定任务的标准费率计算并返回总费用（即在对估计小时数进行任何进一步更改之前。代码脚本，包括用于测试每个功能的SELECT语句+每个测试语句输出的“屏幕截图”。* /

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) 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. */

最满意答案

您似乎正在使用HAVING来应用简单的WHERE条件。 相同的例子：

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>

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>