Description

The programs selects sales and profit information from database "Sample.accdb".
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.accdb  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.accdb".
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

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

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, *.accdb);dbq=.\Sample.accdb" 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, *.accdb);dbq=.\Sample.accdb" 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, *.accdb)};dbq=.\Sample.accdb
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;