Description

This example demonstrates how to read set data from a database file using
SQL2GMS. In particular, it focusses on how to read multi dimensional set elements
and set elements with explanatory text when writing the results to an include
file.

This model is referenced in
   "Example 3 - Reading a one dimensional Set",
   "Example 4 - Reading a multi dimensional Set" and
   "Example 5 - Reading Sets with Explanatory Text"
from the SQL2GMS Documentation.

Keywords: SQL2GMS, data exchange, GAMS language features

Category : GAMS Data Utilities library

Main file : ReadingSets.gms   includes :  Sample.accdb  ReadingSets.gms

$title SQL2GMS Example 3,4,5 - Reading Sets from Database using SQL2GMS (ReadingSets,SEQ=131)

$onText
This example demonstrates how to read set data from a database file using
SQL2GMS. In particular, it focusses on how to read multi dimensional set elements
and set elements with explanatory text when writing the results to an include
file.

This model is referenced in
   "Example 3 - Reading a one dimensional Set",
   "Example 4 - Reading a multi dimensional Set" and
   "Example 5 - Reading Sets with Explanatory Text"
from the SQL2GMS Documentation.

Keywords: SQL2GMS, data exchange, GAMS language features
$offText

$callTool win32.msappavail Excel
$if errorlevel 1 $abort.noError "No Excel available"


* 1. Reading a one dimensional set from the table distances stored in Sample.accdb
$call sql2gms C="DRIVER=Microsoft Access Driver (*.mdb, *.accdb);dbq=.\Sample.accdb" Q="SELECT distinct(city1) FROM distances" O=city_i.inc > %system.nullfile%
$ifE errorLevel<>0 $abort Error reading the one dimensional set using SQL2GMS!

Set i 'canning plants' /
$include city_i.inc
/;

display i;


* 2. Reading multi dimensional set elements
*    Suppose we want to define a two dimensional set ij(i,j) 'canning plants - markets'
*    based on the data of the table distances stored in Sample.accdb
$call sql2gms C="DRIVER=Microsoft Access Driver (*.mdb, *.accdb);dbq=.\Sample.accdb" Q="SELECT city1, city2, ' ' FROM distances" O=city_ij.inc > %system.nullfile%
$ifE errorLevel<>0 $abort Error reading multi dimensional set using SQL2GMS!

* Alternatively, you may run the following statement:
*$call sql2gms C="DRIVER=Microsoft Access Driver (*.mdb, *.accdb);dbq=.\Sample.accdb" Q="SELECT city1&'.'&city2 FROM distances" O=city_ij.inc > %system.nullfile%

Set j 'markets' / new-york, chicago, topeka /;

Set ij(i,j) 'two dimensional set' /
$include city_ij.inc
/;

display ij;


* 3. Reading set elements with explanatory text
* a) Storing the query results as include file
$call sql2gms C="DRIVER=Microsoft Access Driver (*.mdb, *.accdb);dbq=.\Sample.accdb" B Q="SELECT setElement, explText FROM setData" O=setData.inc > %system.nullfile%
$ifE errorLevel<>0 $abort Error reading set with explanatory text using SQL2GMS (output: .inc)!

Set a /
$include setData.inc
/;

* b) Storing the results as GDX file --> use the arguments X and S
$call sql2gms C="DRIVER=Microsoft Access Driver (*.mdb, *.accdb);dbq=.\Sample.accdb" Q="SELECT setElement, explText FROM setData" X=setData.gdx Y=set_b > %system.nullfile%
$ifE errorLevel<>0 $abort Error reading set with explanatory text using SQL2GMS (output: .gdx)!

Set b;
$gdxIn setData.gdx
$load b = set_b
$gdxIn