why not just
select count(*)*3 from load where emp=123 and year=2010;
Are you saying it has to be a function?
To fix your function,
1) don't use the same name for your parameters as the names of your columns.
While not technically wrong, it's bad practice and just creates confusion:
Make it
create or replace function cal_total_credit(p_EMPNUM_i IN number, p_YR_i IN
number, p_hpw_i IN number)
2) always qualify your table names
instead of "select TITLE_CODE from LOAD"
do "select TITLE_CODE from LOAD TL"
3) always qualify your column names
instead of : "select TITLE_CODE:
do:"select tl.TITLE_CODE"
in your case you don't even need title_code because all you want to do is to
count the rows.
It would be better if you said: " select null from load" since you are not
going to use title_code.
4) change " lv_total := lv_total + i.count("TITLE_CODE");" to
" lv_total := lv_total + 1 ;
On Thu, Aug 12, 2010 at 9:40 AM, Richie <cls...@gmail.com> wrote:
> hey good day
>
> i was wondering if anyone could help me out with the code below.
>
> i'm trying the count the total number of courses an employee has and
> to be able to take this number and multiply it by 3.
>
>
> create or replace function cal_total_credit(EMPNUM IN number, YR IN
> number, hpw IN number)
> return number is
> lv_total number := 0;
>
>
> cursor c is
> select TITLE_CODE from LOAD
> where EMPNUM = EMPNUM
> and YR = YR;
>
> BEGIN
> for i in c loop
> lv_total := lv_total + i.count("TITLE_CODE");
> end loop;
>
>
> RETURN ((hpw*13)+(lv_total*3));
>
>
> end cal_total_credit;
>
>
> Thanks in advance
> Richie
>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
