Reading an array from several tables

Especially when working with arrays of more than two dimensions it may happen that the input data is split into several spreadsheet ranges/database tables.

We wish to read an array, INCOME, indexed by the sets CUST and PERIOD from three tables (one table per customer). In the first instance, assume that every data table includes both the CUST and the PERIOD index column, such as (table COLDAT1):

In this case we may read in the data with three statements within a single initializations block:

model "multiple data sources"
 uses "mmodbc"

 declarations
  CUST: set of integer
  PERIOD: range
  INCOME: dynamic array(CUST,PERIOD) of real
 end-declarations

! Method 1: Data in columns, with CUST index value included
 initializations from 'mmodbc.odbc:multitab.sqlite'
  INCOME as 'COLDAT1'
  INCOME as 'COLDAT2'
  INCOME as 'COLDAT3'
 end-initializations

 writeln("1: ", INCOME)

end-model 

The same with SQL statements:

 SQLconnect('multitab.sqlite')
 SQLexecute("select CUST,PERIOD,INCOME from COLDAT1", INCOME)
 SQLexecute("select CUST,PERIOD,INCOME from COLDAT2", INCOME)
 SQLexecute("select CUST,PERIOD,INCOME from COLDAT3", INCOME)
 SQLdisconnect 

Now assume that the input data table for each customer only contains the PERIOD index and the data value itself:

In this case we need to introduce an auxiliary array TEMP with just one index into which we read the data for every customer and that gets copied into the array INCOME. (This method supposes that we know the contents of the set CUST; with the first method this was not required.)

! Method 2: Data in columns, without CUST index value
 procedure readcol(cust:integer, table:string)
  declarations
   TEMP: array(PERIOD) of real
  end-declarations

  initializations from 'mmodbc.odbc:multitab.sqlite'
   TEMP as table
  end-initializations
  forall(p in PERIOD) INCOME2(cust,p):=TEMP(p)
 end-procedure

 forall(c in CUST) readcol(c, "COLDAT"+c+"A")
 writeln("2: ", INCOME) 

If we wish to employ SQL statements for reading the data, the procedure readcol may look as follows (all else remains unchanged):

 procedure readcol(cust:integer, table:string)
  declarations
   TEMP: array(PERIOD) of real
  end-declarations

  SQLexecute("select PERIOD,INCOME from "+table, TEMP)
  forall(p in PERIOD) INCOME(cust,p):=TEMP(p)
 end-procedure

In this case and with the rowwise representation shown below the formulation with SQL statements is likely to be more efficient since we only need to connect once to the data source and then execute a series of `select' commands. For initializations blocks we open and close an ODBC connection with every new block, that is, at every execution of the procedure readcol.

As a third case consider a representation of the data in transposed form, that is, not columnwise but rowwise as shown in the following example table. (This format may occur with spreadsheets but it is certainly less likely, though not impossible, with databases.) With Excel we always need to define a header row for a data range—here we have simply filled it with zeros since its contents is irrelevant. Any row headers written at the left or right of the data range are purely informative, they must not be selected as part of the range.

Such rowwise formatted data may be read with the following Mosel code. As with the previous method, we define a procedure readrow to read data from a single data range. Both index sets, CUST and PERIOD, must be known and the set PERIOD must be finalized (this means that its contents cannot change any more and the set is treated by Mosel similarly to a constant set).

! Method 3: Data in rows, without CUST index value
 procedure readrow(cust:integer, table:string)
  declarations
   TEMP: array(1..2,PERIOD) of real
  end-declarations

  initializations from 'mmsheet.excel:multitab.xls'
   TEMP as 'noindex;'+table
  end-initializations
  forall(p in PERIOD) INCOME3(cust,p):=TEMP(2,p)
 end-procedure

 finalize(PERIOD)                       ! The index sets must be known+fixed
 forall(c in CUST) readrow(c, "ROWDAT"+c)
 writeln("3: ", INCOME3) 

The corresponding SQL code looks as follows (notice the setting of the parameter SQLndxcol):

 procedure readrow(cust:integer, table:string)
  declarations
   TEMP: array(1..2,PERIOD) of real
  end-declarations

  SQLexecute("select * from "+table, TEMP)
  forall(p in PERIOD) INCOME3(cust,p):=TEMP(2,p)
 end-procedure

 finalize(PERIOD)                 ! The index sets must be known+fixed
 setparam("sqlndxcol",false)      ! Data specified in dense format (no indices)
 forall(c in CUST) readrow(c, "ROWDAT"+c)
 setparam("sqlndxcol",true) 

To read the data from a database using the odbc driver instead of excel we merely need to change the file name:

 initializations from 'mmodbc.odbc:skiph;multitab.sqlite'
  ...