ReadingSets.gms : SQL2GMS Example 3,4,5 - Reading Sets from Database using SQL2GMS

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.mdb  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

$if %system.filesys% == UNIX $abort.noError 'This model cannot run on a non-Windows platform';


* 1. Reading a one dimensional set from the table distances stored in Sample.mdb
$call sql2gms C="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample.mdb" 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.mdb
$call sql2gms C="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample.mdb" 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="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample.mdb" 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="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample.mdb" 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="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample.mdb" 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