Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!swrinde!elroy.jpl.nasa.gov!ames!uhccux!munnari.oz.au!brolga!exnirad
From: exnirad@brolga.cc.uq.oz.au (Nirad Sharma)
Newsgroups: comp.databases
Subject: Re: Oracle Embedded SQL FETCH into array Question
Keywords: Oracle, SQL
Message-ID: <1991Apr11.231039.13428@brolga.cc.uq.oz.au>
Date: 11 Apr 91 23:10:39 GMT
References: <1057@ejs700.ejs.is>
Organization: Prentice Computer Centre, The University of Queensland, Australia.
Lines: 53

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