Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!pacific.mps.ohio-state.edu!linac!midway!mimsy!rlgvax!ccicpg!cci632!pjc
From: pjc@cci632.cci.com (Patrick Conley)
Newsgroups: comp.databases
Subject: Re: Oracle Embedded SQL FETCH into array Question
Keywords: Oracle, SQL
Message-ID: <1991Apr12.112437.2014@cci632.cci.com>
Date: 12 Apr 91 11:24:37 GMT
Reply-To: pjc@ccird7.UUCP (Patrick Conley)
Organization: CCI, Communications Systems Division, Rochester, NY
Lines: 28

>>Assume a row in a table FISCALYEAR has fields called
>>PROFIT1, PROFIT2, ... , PROFIT12 and LOSS1, LOSS2, ... , LOSS12
>>(meaning profits and losses in months #1 to #12 of the year).

Though there are no doubt ways to do this (as other postings will
show), I think the basic problem is one of table design.  This is 
not the  normal way to design a "relational" table with repeating
data.  A more standard way would be to seperate out the data
asociated with each month (to use your example).  Such as:

Month | Profit | Loss
------+--------+-------
1     |  xxx   | yyy
2     |  zzz   | aaa
.
.
.
11    |  bbb   | ccc
12    |  eee   | fff

Then several of the SQL functions will work more naturally.  For example
selecting the month with the greatest profit (or loss, or difference).
And your question, selecting into the array, will work as you described.

Nearly any time I see fld1, fld2, fld3... in a relational table it
raises a warning to me about table design.

Good Luck.