SalesProfitDB5.gms : SQL2GMS Example 2 - Reading a multi valued Table

Description

The programs selects sales and profit information from database "Sample.mdb".
In the first place, two separate parameters and SQL2GMS calls are used in order
to write the results to "sales.inc" and "profit.inc".
Afterwards, the data is accessed using "UNION" and the results are written to
"data.inc".
Alternative connection strings are also provided as comment.

This model is referenced in "Example 2 - Reading a multi valued Table" from
the SQL2GMS Documentation.

Keywords: SQL2GMS, data exchange, GAMS language features


Category : GAMS Data Utilities library


Main file : SalesProfitDB5.gms   includes :  Sample.mdb  SalesProfitDB5.gms

$title SQL2GMS Example 2 - Reading a multi valued Table (SalesProfitDB5,SEQ=048)

$onText
The programs selects sales and profit information from database "Sample.mdb".
In the first place, two separate parameters and SQL2GMS calls are used in order
to write the results to "sales.inc" and "profit.inc".
Afterwards, the data is accessed using "UNION" and the results are written to
"data.inc".
Alternative connection strings are also provided as comment.

This model is referenced in "Example 2 - Reading a multi valued Table" 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';

Set
   year 'years'     / 1997*1998 /
   loc  'locations' / nyc, was, la, sfo  /
   prd  'products'  / hardware, software /
   type 'data type' / sales, profit      /;


* Section: Two separate Parameters
$call sql2gms C="DRIVER={Microsoft Access Driver (*.mdb)};dbq=Sample.mdb" Q="SELECT year, loc, prod, sales FROM data" O=sales.inc > %system.nullfile%
$ifE errorLevel<>0 $abort Error using SQL2GMS when querying sales!

Parameter sales(year,loc,prd) /
$include sales.inc
/;

$call sql2gms C="DRIVER={Microsoft Access Driver (*.mdb)};dbq=Sample.mdb" Q="SELECT year, loc, prod, profit FROM data" O=profit.inc > %system.nullfile%
$ifE errorLevel<>0 $abort Error using SQL2GMS when querying profit!

Parameter profit(year,loc,prd) /
$include profit.inc
/;

display sales, profit;


* Section: Single Parameter with extra Index Position
$onText
The query statement can be divided into multiple lines if required as follows:
Q=SELECT year, loc, prod, 'sales', sales FROM data \
  UNION \
  SELECT year, loc, prod, 'profit', profit FROM data
$offText

$onEcho > howToRead.txt
C=DRIVER={Microsoft Access Driver (*.mdb)};dbq=Sample.mdb
Q=SELECT year, loc, prod, 'sales', sales FROM data UNION SELECT year, loc, prod, 'profit', profit FROM data
O=data.inc
$offEcho

$call sql2gms @howToRead.txt > %system.nullfile%
$ifE errorLevel<>0 $abort Error using SQL2GMS!

Parameter data(year,loc,prd,type) /
$include data.inc
/;
display data;

$onText
Alternatively, following connection strings might also be used
C=DSN=MS Access Database;dbq=Sample.mdb
C=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample.mdb
C=DSN=Sample
Note that for the last connection string to work, Sample.mdb must be entered as
'User DSN' in 'Control Panel | Administrative Tools | Data Sources (ODBC)'.
$offText