Re: INSERT INTO and MULTI-RECORD Pls Help!

<times9_at_my-deja.com> wrote in message news:8i5rs2$8m5$1_at_nnrp1.deja.com...
>Thank you for the help.
>I have a PAYROLL table which has the student name, amount earned, pay period,
year, and the social security number.

Okayyyy...like this?:

STUDENT_NAME AMT_EARNED PAY_PER YR SSN

------------  ---------- --------- ---- -----------
Frasier Crane     500.00 SUMMER1   1999 123-45-6789
Frasier Crane     500.00 SUMMER2   1999 123-45-6789
Frasier Crane     500.00 SUMMER3   1999 123-45-6789
Frasier Crane     750.00 ACADEMIC1 1999 123-45-6789
Frasier Crane     750.00 ACADEMIC2 1999 123-45-6789
Frasier Crane     750.00 ACADEMIC3 1999 123-45-6789
Niles Crane       500.00 SUMMER1   1999 223-88-5547
Niles Crane       500.00 SUMMER2   1999 223-88-5547
Niles Crane       500.00 SUMMER3   1999 223-88-5547
Niles Crane       750.00 ACADEMIC1 1999 223-88-5547
Niles Crane       750.00 ACADEMIC2 1999 223-88-5547
Niles Crane       750.00 ACADEMIC3 1999 223-88-5547

>What I'm trying to do is: for each student, sum up the amount earned for the
pay periods for the summer semesters and the fiscal academic semesters seperately. Pay periods differ for summer and academic semesters. Then I need to display these totals in a multi-record block for summer and academic year in 2 different multi-record blocks. I wanted to do a SELECT and insert what it returns into the multi-record block, but I don't know how to insert into it.

We'll work on that in a second...

>Oracle forms says something like, "There are too many rows returned."

[Quoted] Sounds like you're doing a "SELECT...INTO", which will only work with one and [Quoted] only one row. These are OK if the situation warrants it, but generally you [Quoted] [Quoted] also need to trap the NO_DATA_FOUND and TOO_MANY_ROWS system exceptions when [Quoted] [Quoted] you use a SELECT...INTO. Sounds like you need a declared cursor that you can loop through.

>There is a multi-record block called SUMMER_PAYROLL which has columns name,
amount earned, social security number.

Ah - I see.

>And I issued:
>
>select name, SUM(amount_earned), ssn
>INTO :SUMMER_PAYROLL.name, :SUMMER_PAYROLL.amount_earned,
> :SUMMER_PAYROLL.ssn from PAYROLL
>where year = 2000 and pay_period < 15;
>
>The select will return multiple records, and I want it to populate the
multi-record block SUMMER_PAYROLL. But it doesn't work.

I think you're missing a "GROUP BY name, ssn" - that might be the problem. "SUM" is an aggregate function (acts across one or more rows) and you need to do a GROUP BY on the other columns that aren't aggregated (name and ssn).

Try that and if that doesn't work, post again and we'll have another shot at it.

-Matt Received on Wed Jun 14 2000 - 00:00:00 CEST