Sunday, October 21, 2007

PL/SQL: ORA-01744: inappropriate INTO

I have this part of the stored procedure, trying to use one select query to fill in 2 variables, got error 'PL/SQL: ORA-01744: inappropriate INTO'

--print summary of subsection

select t.* from (select count(membership.id) into v_tot_mem_by_trainer,sum((membership_plan.monthly_price- membership.monthly_discount) * round(months_between(sysdate, membership.join_date),0)) into v_tot_rev_by_trainerfrom membership, membership_planwhere membership.trainer_id = r_trainer.trainer_id andmembership.membership_plan_id = membership_plan.id) t;


this is the original select query that has no problem
select t.* from (select count(membership.id) as total_number_of_members,sum((membership_plan.monthly_price- membership.monthly_discount) * round(months_between(sysdate, membership.join_date),0)) as total_membership_paymentfrom membership, membership_planwhere membership.membership_plan_id = membership_plan.id) t;


the solution:

select t.* into v_tot_mem_by_trainer, v_tot_rev_by_trainer from (select count(membership.id),sum((membership_plan.monthly_price- membership.monthly_discount) * round(months_between(sysdate, membership.join_date),0))from membership, membership_planwhere membership.trainer_id = r_trainer.trainer_id andmembership.membership_plan_id = membership_plan.id) t;

No comments: