te@ejs.is (Tryggvi Edwald) (04/11/91)
Hi, all.
I know that I can FETCH from a table INTO an array, "vertically", i.e. take,
say, 100 rows into a 100-element array in one go.
What I would like to do, however, is fill an array "horizontally", from a
single row fetch.
An example:
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).
I have been trying to find a way to FETCH these into arrays, say prof[0] to
prof[11], and loss[0] to loss[11], so that some net outcome can be calculated
like:
outcome = 0.;
for( i=0; i<12; i++ ){ outcome += ( prof[i] - loss[i] ) }
...
(This example is synthetic, just to explain what I mean.)
The problem is, of course, that I have not been able to find a way to do this.
I would appreciate almost any comment on this.
Greetings.
Tryggvi Edwald, te@ejs.is, ICELAND | Don't bother flaming me and calling
Einar J Skulason hf, Grensasvegi 10 | me an idiot. I already know that from
108 Reykjavik, ICELAND | different sources. (Previous postings)exnirad@brolga.cc.uq.oz.au (Nirad Sharma) (04/12/91)
te@ejs.is (Tryggvi Edwald) writes: >Hi, all. >I know that I can FETCH from a table INTO an array, "vertically", i.e. take, >say, 100 rows into a 100-element array in one go. >What I would like to do, however, is fill an array "horizontally", from a >single row fetch. >An example: >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). >I have been trying to find a way to FETCH these into arrays, say prof[0] to >prof[11], and loss[0] to loss[11], so that some net outcome can be calculated >like: > outcome = 0.; > for( i=0; i<12; i++ ){ outcome += ( prof[i] - loss[i] ) } > ... >(This example is synthetic, just to explain what I mean.) Using c (I assume from your example that you are using c) you could use the following steps : 1. Define the host variables as if a normal retrieval is to be done i.e EXEC SQL DECLARE etc. float PROFIT1, PROFIT2, .. LOSS1, LOSS2 2. Create an array of pointers to type float for profit & loss : i.e. float *profit[NUM_VALS], *loss[NUM_VALS]; 3. Assign each of the pointers in profit, loss to the locations of the host variables : profit[0] = &PROFIT1; loss[0] = &LOSS1; profit[1] = &PROFIT2; loss[1] = &LOSS2; This only need be done once for the particular query. Now, just EXEC SQL SELECT a row at a time and use the following modification to you example code to dereference the pointers for each tupple : outcome = 0.; for( i=0; i<12; i++ ){ outcome += ( *(prof[i]) - *(loss[i]) ) } ... To use Oracle array fetches in conjunction with this some additional playing around with the pointers should do the job. I hope this is what you wanted. -- Nirad Sharma (exnirad@brolga.cc.uq.oz.au) Phone : (+61 7) 365 7575 Systems Programmer Fax : (+61 7) 870 5080 Continuing Education Unit The University of Queensland. QLD 4072
pjc@cci632.cci.com (Patrick Conley) (04/12/91)
>>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.